How can I do "use @dbname"

Question

Post reply

How can I do "use @dbname"

Carl Uman

SSCommitted

Points: 1993
More actions
December 21, 2005 at 4:00 pm

#90264

I tried to search for this but I had a hard time finding anything. The word "use" tends to return a lot of results.
Anyhow I want to loop through my DBs running some TSQL in each one. The problem is that if I do "use @dbname" I get "Incorrect syntax near '@DBName'". If I do "exec ('use @dbname')" it will only change the current DB for the duration of that single exec statement so my following TSQL will run in the DB I started out in.
Anyhow I hope that makes sence if not I'll try to clear it up.
Thanks
Carl

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TimS Ten Centuries Points: 1165 More actions · Answer 1

I would try using sp_MSforeachdb

see http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm

Tim S

The Aethyr Dragon SSChasing Mays Points: 636 More actions · Answer 2

Hi Carl,

I've got a script that does what you are trying to do, but I don't have any problems with it not staying on the database. Are you running the T/SQL in QA or in a Job etc?

The syntax that I'm using is as follows:

EXEC ('USE ' + @C_DBName)

My script is below so that you can see the context, I'm changing the current database so that I can set a Default value on a temp table for collection of permission info per database object - but sp_helprotect must run on the actual database!

Cheers

The Aethyr Dragon

Declare @C_DBName varchar(250),

@D_Today varchar(20),

@bcpResult INT,

@bcpCommand NVARCHAR(4000),

@sqlQuery NVarchar(4000),

@GenFileName varchar(500)

set @D_Today = convert(varchar(20), getdate(), 111)

Print '---> Dropping Temp Permissions Table'

if exists (select * from tempdb..sysobjects where name = '##TempPermDets')

Begin

Drop Table ##TempPermDets

end

Print '---> Declaring DB_Cursor'

Declare LoopDB_Cursor Cursor

global scroll dynamic

For Select RTrim(Name)

From master..sysdatabases order by name

Print '---> Opening Cursor'

Open LoopDB_Cursor

Print '---> Fetch First'

Fetch Next From LoopDB_Cursor into @C_DBName

Print '---> Create Table'

Create Table ##TempPermDets(

ServerName varchar(250) DEFAULT(@@SERVERNAME),

DateRun Varchar(30) DEFAULT convert(varchar(30), getdate(), 111),

DBName varchar(250),

Owner varchar(250),

Object Varchar(250),

Grantee Varchar(100),

Grantor Varchar(100),

ProtectType Varchar(100),

Permission Varchar(50),

ColName varchar(250)

)

INSERT INTO ##TempPermDets

VALUES('SERVERNAME', 'DATERUN', 'DATABASENAME', 'OBJECTOWNER', 'OBJECTNAME', 'GRANTEE',

'GRANTOR', 'PROTECTTYPE', 'PERMISSION', 'COLUMNNAME')

Print '---> Start Loop'

While @@Fetch_Status = 0

Begin

EXEC ('ALTER TABLE ##TempPermDets ADD CONSTRAINT

DF_temp_dbname DEFAULT (''' + @C_DBName + ''') FOR DBName')

EXEC ('USE ' + @C_DBName)

Insert into ##TempPermDets(Owner, Object, Grantee, Grantor, ProtectType, Permission, ColName)

exec sp_helprotect

Fetch Next From LoopDB_Cursor into @C_DBName

ALTER TABLE ##TempPermDets

DROP CONSTRAINT DF_temp_dbname

End

Close LoopDB_Cursor

Deallocate LoopDB_Cursor

SET @sqlquery = 'SELECT * From ##TempPermDets'

SET @GenFileName = 'C:\ServerInfo\' + @@ServerName + '_ObjectPermissions_' + CONVERT(Varchar(15), GetDate(), 112) + '.CSV'

SET @bcpCommand = 'bcp "' + @sqlquery + '" queryout "' + @GenFileName + '" -t, -c'

-- Export to CSV

EXEC @bcpResult = master..xp_cmdshell @bcpCommand

The Aethyr Dragon

Cape Town
RSA

Carl Uman SSCommitted Points: 1993 More actions · Answer 3

Running from QA this script will give you a list of tables for whatever DB you have in the dropdown in QA once for every DB in the query. Notice it does not give you a list of tables for each DB but repeats it for the BD in the dropdown from QA (just making sure that point is understood).

Not sure if I missed the boat here but it's not working for me. And no sp_tables is not what I want to run but rather a bunch of code that will eventually reindex only the tables that need it in each DB so I don't believe sp_MSforeachdb will not work for me.

Thanks

Carl

SET NOCOUNT ON

DECLARE @DBName VARCHAR(100)

DECLARE curDBs CURSOR STATIC LOCAL

FOR

SELECT Catalog_Name

FROM Information_Schema.Schemata

WHERE [Catalog_Name] NOT IN ('MODEL','TEMPDB')

OPEN curDBs

FETCH NEXT

FROM curDBs

INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

-- Set working DB

EXEC ('USE ' + @DBName)

exec sp_tables

-- Get next DB

FETCH NEXT FROM curDBs INTO @DBName

END

CLOSE curDBs

DEALLOCATE curDBs

The Aethyr Dragon SSChasing Mays Points: 636 More actions · Answer 4

Sorry about that, you need to put the statements in with the EXEC - I was having the same problem - I was concentrating on the syntax!!!

EG: exec('USE ' + @DBName + ' exec sp_tables')

Then it works fine. If you've got a bunch of code then I'd suggest putting it into a stored proc and calling it. The other alternative is to put every statement into it's own exec('USE ' + @DBName + ' {next line of code...})

Cheers

The Aethyr Dragon

Cape Town
RSA