How can I do something like: USE @databaseName

Question

 DECLARE @DatabaseName NVARCHAR(max); SET @DatabaseName = 'MainDb'
 USE @DatabaseName

Wouldn't work. How to make it?

Answer 1

EXEC('USE ' + @DatabaseName + ';SELECT --etc')

So long as you trust @DatabaseName to not contain ;DROP DATABASE MyDB :)

Answer 2

You will have to use Dynamic SQL to achieve this.

Before you start exploring Dynamic SQL, I suggest you read this excellent article http://www.sommarskog.se/dynamic_sql.html

Answer 3

You'd have to use dynamic SQL if you want to do it dynamically like that. Would mean anything you want to execute under the context of that DB, you'd need to include in the dynamic SQL statement too.

i.e. assume you want to list all the tables in MainDB:

This won't work, as the USE statement is in a different context - once that EXECUTE has run, the following SELECT will NOT be running in that same context and so won't be running in MainDb (unless the connection was already set to MainDb)

DECLARE @DatabaseName NVARCHAR(MAX)
SET @DatabaseName = 'MainDb'
EXECUTE('USE ' + @DatabaseName) -- SQL injection risk!
SELECT name FROM sys.tables

So you'd need to do:

DECLARE @DatabaseName NVARCHAR(MAX)
SET @DatabaseName = 'MainDb'
EXECUTE('USE ' + @DatabaseName + ';SELECT name FROM sys.tables') -- SQL injection risk!

Of course, you need to be very careful with SQL injection, for which I point you to the link in Barry's answer.

Answer 4

If you need to do this as part of deployment process or some backend process as opposed to somthing kicked off by a user an alternative to putting everything in dynamic statements like this

 EXECUTE('USE ' + @DatabaseName + ';select * from INFORMATION_SCHEMA.TABLES;
          select * from INFORMATION_SCHEMA.COLUMNS;
          select * from INFORMATION_SCHEMA.ROUTINES;
          select * from INFORMATION_SCHEMA.PARAMETERS;')

you can go Old School, by using the SQLCMD utility and using your favorite scripting program. I would recommend PowerShell but for this sample I'll use classic DOS batches.

Assume you have the file C:\input.sql that looks like this

select * from INFORMATION_SCHEMA.TABLES;
select * from INFORMATION_SCHEMA.COLUMNS;
select * from INFORMATION_SCHEMA.ROUTINES;
select * from INFORMATION_SCHEMA.PARAMETERS;

You can execute that input.sql on multiple dbs by putting the following a batch file C:\Test.bat (this batch assumes in the same directory as input.sql)

C:\Test.bat

set var=maindb

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -d %var% -i"input.sql"

set var=master

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -d %var% -i"input.sql"

Then you can execute it by

C:\>Test.bat

The advantages to this approach are

• you can develop your Input.SQL as you would normally would
• It also doesn't have the Varchar limitation that EXECUTE has..
• Lots of options with scripting PowerShell, VBS, MS DOS Batch, Shell Execute
• Lot of SQLCMd options (output file , timeouts, etc.)

Answer 5

If you're running your script in SSMS, you could use SQLCMD Mode (found under the Query menu) to script a variable for your database name.

:setvar database "MainDb"
use $(database)
go

select * from sys.tables

Answer 6

Instead of dynamic SQL to do the equivalent of USE @Database, may I submit that some "pre-processed dynamic SQL" could be an even better solution for you? Of course, it depends on why you need a dynamic USE statement. I'm assuming that you truly can't from the start use the correct database from your connection string (which is really the best way to handle this).

CREATE SYNONYM dbo.CustomName FOR SomeDatabase.SomeSchema.SomeObject

Then in the stored procedure or whatever it was you wanted to do after your dynamic USE statement, just refer to dbo.CustomName.

To switch things around easily, you could create a little infrastructure. Build a table with the synonym aliases and what object they'll map to. Create a stored procedure that reads this table and runs dynamic SQL to update your SYNONYMs.

When you need to switch, run that SP and it will rip through all the objects you need and relink them.

This strategy won't work if you need various processes accessing different databases at the same time through the synonyms. In that case you're better off with some other method.

Keep in mind that you can still avoid dynamic SQL in some clever ways. For example, maybe instead of putting the SP you want to run in the main database and having it perform its manipulations on each subdatabase dynamically, put the SP in each sub-database and then call each SP.