USE @DBNAME

Question

Post reply

USE @DBNAME

Warren Peace

SSCertifiable

Points: 5881
More actions
April 28, 2008 at 10:51 am

#189640

I created a cursosr with a list of all DB's on the server. I want to iterate through the list and run a command against each DB. The following is a snipit of the code that uses the USE statement with a variable. I keep getting the following error...
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@DBNAME'.
Is this even possible?
DECLARE @DBNAME varchar(50)
SET @DBNAME = '_RESTORERECOVER'
USE @DBNAME
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You cannot use "USE" with a variable.

You also don't need to create a cursor - there is a system procesure to help you.

Try something like this:

EXEC sp_MSforeachdb @Command1 = '

USE ?

SELECT DB_Name()'

Warren Peace SSCertifiable Points: 5881 More actions · Answer 2

I tried as you suggested...

DECLARE @DBNAME varchar(50)

DECLARE @MyCommand varchar(1000)

SET @DBNAME = '_RESTORERECOVER'

PRINT @DBNAME

SET @MyCommand = 'USE [' + @DBNAME + ']'

EXECUTE(@MyCommand)

But the DB never switched to the _RESTORERECOVER DB.

I ran it in SSMS. The command "USE [_RESTORERECOVER]" switched to the DB but for some reason executing from a variable doesn't work.

Any ideas?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Warren Peace (4/28/2008)
I tried as you suggested...
DECLARE @DBNAME varchar(50)
DECLARE @MyCommand varchar(1000)
SET @DBNAME = '_RESTORERECOVER'
PRINT @DBNAME
SET @MyCommand = 'USE [' + @DBNAME + ']'
EXECUTE(@MyCommand)
But the DB never switched to the _RESTORERECOVER DB.
I ran it in SSMS. The command "USE [_RESTORERECOVER]" switched to the DB but for some reason executing from a variable doesn't work.
Any ideas?

You're running into a scoping issue. The EXECUTE switches database, in its own execution context. It does NOT change the main execution context. So in short - you script opens a separate context, runs a USE statement to change database, does nothing, and then exits (so you don't see anything happening).

In order for that to work the way you want - you'd have to add in to the @Mycommand what you want to query for/execute in each database. Of course - if you are running it against every database - that's what the sp_forEachDB does (as was previous suggested).

Your script might look something like:

DECLARE @DBNAME varchar(50)

DECLARE @MyCommand varchar(1000)

SET @DBNAME = '_RESTORERECOVER'

PRINT @DBNAME

SET @MyCommand = 'USE [' + @DBNAME + '];Select * from sys.columns;'

EXECUTE(@MyCommand)

You should get a column listing from each database....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

It would help if you posted the remainder of what you are trying to do.

Right now, you have a USE statement with no commands after it.

I assume there is more to it.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 5

Here's the code...

And yes I know I can create an SSIS package to do this. I just want to be able to script it so I may, in the future, add other tasks that will affect all user DB's.

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.#tmpDBLIST') AND type in (N'U'))

DROP TABLE [dbo].#tmpDBLIST

CREATE table #tmpDBLIST(

DATABASE_NAME varchar(250),

DATABASE_SIZE INT,

REMARKS varchar(250))

INSERT INTO #tmpDBLIST EXEC sp_databases

-- Select * From #tmpDBLIST Order By DATABASE_NAME ASC

-- Scrubout system DB's...

DELETE FROM #tmpDBLIST WHERE DATABASE_NAME = 'master'

OR DATABASE_NAME = 'DBEMPTY'

OR DATABASE_NAME = 'model'

OR DATABASE_NAME = 'msdb'

OR DATABASE_NAME = 'pubs'

OR DATABASE_NAME = 'tempdb'

DECLARE DBListCursor CURSOR

READ_ONLY

FOR SELECT DATABASE_NAME FROM #tmpDBLIST

DECLARE @DBNAME varchar(250)

DECLARE @MyCommand varchar(1000)

OPEN DBListCursor

FETCH NEXT FROM DBListCursor INTO @DBNAME

WHILE (@@fetch_status <> -1)

BEGIN

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SET @MyCommand = 'USE [' + @DBNAME + ']'

EXECUTE(@MyCommand)

PRINT @MyCommand

--DECLARE @TableNameToReIndex VarChar(200)

--DECLARE ListOfTables CURSOR FOR

--SELECT table_name

--FROM information_schema.tables

--WHERE table_type = 'base table'

--Order By table_name ASC

--

--OPEN ListOfTables

--FETCH NEXT FROM ListOfTables INTO @TableNameToReIndex

--WHILE @@FETCH_STATUS = 0

--

--BEGIN

-- PRINT 'Reindexing ' + @TableNameToReIndex + ' table' + ' ' + 'DBCC DBREINDEX (' + @TableNameToReIndex + ', ' + Char(39) + ' ' + Char(39) + ', 80)'

-- --DBCC DBREINDEX (@TableNameToReIndex, ' ', 80)

-- FETCH NEXT FROM ListOfTables INTO @TableNameToReIndex

--END

--

--CLOSE ListOfTables

--DEALLOCATE ListOfTables

--PRINT @DBNAME

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FETCH NEXT FROM DBListCursor INTO @DBNAME

END

CLOSE DBListCursor

DEALLOCATE DBListCursor

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

There are probably a bunch of scripts for doing this, but I wanted to see if I could get something with just a few lines of code. I added a couple of example IF statements into it so it would only reindex one table on my server, but they could be used with a NOT IN for exclusion of system databases or specific tables.

This is full of nested calls, so the single quotes get a bit out of hand.

[font="Courier New"]EXEC sp_MSForEachDB 'PRINT ''Database: ?''

USE [?];

IF DB_Name() = ''IBSDWArchive''

EXEC sp_MSForEachTable ''IF ''''^'''' = ''''[Base].[StgDimControl]''''

EXEC(''''DBCC DBREINDEX("^","",80)'''')'',''^''

ELSE

PRINT '' Database ? Excluded'''[/font]

the__t Hall of Fame Points: 3210 More actions · Answer 7

Matt Miller (4/28/2008)
In order for that to work the way you want - you'd have to add in to the @Mycommand what you want to query for/execute in each database.
Your script might look something like:
DECLARE @DBNAME varchar(50)
DECLARE @MyCommand varchar(1000)
SET @DBNAME = '_RESTORERECOVER'
PRINT @DBNAME
SET @MyCommand = 'USE [' + @DBNAME + '];Select * from sys.columns;'
EXECUTE(@MyCommand)
You should get a column listing from each database....

Matt is correct, in order to use a variable name as part of your USE statement and actually execute any commands against the database you are now "using", you need to include the whole thing in @MyCommand using concatenation. Then one EXECUTE(@MyCommand) will do the trick. It won't work otherwise.

Mimic exactly what Matt has posted in your own query - just be sure to get all the quotes right - the annoying part.

"Got no time for the jibba jabba!"
-B.A. Baracus