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

Wouldn't work. How to make it?

Improve this question
1

8 Answers 8

Reset to default
39

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I would like to emphasize what AdaTheDev said - dynamic SQL can be very dangerous, unless handled correctly.
Now I have another problem. I'm running a script in SQL Management Studio. And I want to define the database in the variable. You've told me how to do that. But still for some reason inside a transaction, adding constraint with a reference doesn't work. It works if I manually chose the database, otherwise it couldn't find the table that constraint refer to.
@Ike: If you're running this in SSMS, see my answer for another possible alternative.
If I need to have the database name in a variable, then there's a huge code smell here.
As with most things it's all about context, pros/cons and right approach for the right job. In some multi-tenancy solutions, you will have multiple databases designed exactly alike and where they are provisioned dynamically/automatically, you will not know the database name at design time. End of the day, it can be a sign of an issue/smell, but as with most things, "It Depends"
|
18

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
Improve this answer

3 Comments

This sounds great, but how do you use a variable instead of "MainDb"?
@RonJohn database is the variable in this example.
I think that @RonJohn meant was that there doesn't appear to be a way to assign a value to a SQLCMD variable dynamically. For example, if you're looping through a list of databases generated by a SELECT statement, there doesn't appear to be a way to assign the current database to the SQLCMD variable.
8

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.

Improve this answer

31 Comments

> 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. This is the main shortcoming of this approach, it only supports one external database.
@tbone Yeah, I kind of said that. :) Thanks for pointing out how awesome my answer is to include this sort of supporting helpful information. How about a +1 for such a thorough discussion of all the pitfalls inherent in this otherwise useful approach?
@tbone I'd like to add, though, that the requirement for different processes to access different databases is a very unusual one, and to my intution, points to some fundamental architectural design flaw in the system. In some way, things that are in actuality different, are being treated as if they are the same. The DRY principle can be misused in situations where one thinks that repetition is being avoided, except, it's not really a repeat after all...
Having multiple instances of the same database is certainly unusual, but it's not fundamentally wrong. The fundamental architecture flaw here is in SQL server's inability to easily access different databases at runtime in a simple and safe way, instead forcing us to use dynamic sql which both sucks and opens up possibility of injection, etc.
@tbone How is it possible that you know the schema at design time but don't know the database? Maybe the script should live in the source code, or the source database? Maybe there should be one version of the script per database? Maybe the caller should connect to the source database directly? Maybe there should be a service that abstracts away this problem, a middle later or tier? There are plenty of reasons such a requirement as you suggest may be less than optimal.
|
3

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.)
Improve this answer

Comments

3

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
Improve this answer

1 Comment

This is a duplicate of my answer, which was provided 6 years earlier.
2 
EXEC('USE ' + @DatabaseName + ';SELECT --etc')

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

Improve this answer

2 Comments

This only sets the context for the dynamic sql statement, not the outer scope.
Sorry, I thought that went without saying, reading back I can see why it looks like that. The original question didn't give any extra SQL to execute so I didn't put anything else in there.
1

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

Improve this answer

Comments

0

Try this

DECLARE @db AS VARCHAR(100); DECLARE @sql AS NVARCHAR(1000); SET @db = 'db_name'; SET @sql = 'USE ' + QUOTENAME(@db); EXEC sp_executesql @sql;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.