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

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.

To prevent SQL Injection, you could also use QUOTENAME() function, it wraps parameter in square brackets:

DECLARE @DatabaseName sysname = 'MainDb'
    , @SQL NVARCHAR(MAX);

SET @SQL = N'USE ' + QUOTENAME(@DatabaseName);

PRINT(@SQL);
-- USE [MainDb]

EXECUTE(@SQL);

Answer 2

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 3

Use Synonyms Instead

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).

The dynamic SQL I'm suggesting is to start out by creating a synonym for each object you want to reference:

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.

Caveat

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.

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

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

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

Answer 6

I found the mix of SYNONYM and exec dynamic SQL the only thing I got to work (esp as part of a stored procedure with multiple databases). A simplified version of what worked for me to achieve querying a database from a variable name.

CREATE PROCEDURE DM_TCM_TO_COMCARE 
    @FROM_DB varchar(100) = '',
    @TO_DB varchar(100) = ''
AS
BEGIN
    --CHECK INPUT VARIABLES

    DROP SYNONYM dbo.From_TableA 
    SET @SQL_SCRIPT = 'CREATE SYNONYM dbo.From_TableA FOR ['+@FROM_DB+'].[dbo].[TableA]'
    exec (@SQL_SCRIPT)

    DROP SYNONYM dbo.To_TableB
    SET @SQL_SCRIPT = 'CREATE SYNONYM dbo.To_TableB FOR ['+@TO_DB+'].[dbo].[TableB]'
    exec (@SQL_SCRIPT)

    select * from dbo.From_TableA
    select * from dbo.To_TableB

    insert into dbo.To_TableB 
         select * from dbo.From_TableA where 1 = 1
    -- etc
END
GO

Answer 7

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