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:'

No comments:

Post a Comment