Showing posts with label db_name. Show all posts
Showing posts with label db_name. Show all posts

Thursday, March 29, 2012

cursor select and variables

I have problems to place my variable into the select statement.

DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @.DB_Name in my select ?

the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.

declare @.sql VARCHAR(4000)

set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'

EXEC (@.sql)

Monday, March 19, 2012

Current Database name

in T-SQL how do I check what is the current database name?
In my script, I used:

.
.
.
OPEN DBList
FETCH NEXT FROM DBList INTO @.DB_name

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQLString = N'USE ' + @.DB_name
EXEC (@.SQLString)
.
.
.
Somehow it always stayed "Master" DB and never on go to the next.

ThanksA1. select db_name()
A2. Dynamic exec runs in separate query/security context, subcontext of current query. If you want to run some code in different database context and you select variable database name, you must put this code together with USE into dynamic code.|||Q1 in T-SQL how do I check what is the current database name?

A3 It is not clear what the purpose of the script is. If what is needed is a list of DBs consider using sp_Databases or the Information_Schema.Schemata view, for example:

Exec sp_Databases

Select Catalog_Name From Information_Schema.Schemata

A4 There are several ways to "check what is the current database name", consider Db_Name(), (as Ispaleny already suggested, which is also the simplest), or the Information_Schema views, for example:

Use Pubs
Go

Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables

Select Db_Name() As 'The Current DB Name using Db_Name() is:'

Use Tempdb
Go

Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables

Select Db_Name() As 'The Current DB Name using Db_Name() is:'