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?

Nisen

Strongly related: How to use a variable for the database name in T-SQL? – Zebulon

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

Arella

I would like to emphasize what AdaTheDev said - dynamic SQL can be very dangerous, unless handled correctly. – Baalman

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. – Nisen

@Ike: If you're running this in SSMS, see my answer for another possible alternative. – Darned

If I need to have the database name in a variable, then there's a huge code smell here. – Triny

@Triny - possibly, possibly not. In my experience, this kind of thing comes in handy in multi-tenancy systems. – Arella

@HLGEM: I've also seen a similar need in deployment scripts where the DBs are named differently in dev, QA and production environments (which, I realize, could be the subject of a whole separate debate). – Darned

@Triny Why is this a code smell? This type of thing is absolutely routine in any other language that access a database, TSQL is the only language where you can't do it. – Seligman

It is a code smell because there is no way that you should not know the database name at design time. If for instance you have different names on dev and prod, that is a poor practice. If you have multiple databases that are designed exactly alike, that is a poor practice. If you are trying to do everything dynamically that is just outright stupid. – Triny

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" – Arella

No dynamic SQL is ever really safe, but you can do SELECT name FROM sys.databases where name = @db on its own query before running the dynamic SQL. (Don't just add it to the script). It will "help" ensure you're not getting injected. – Haemophilia

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

Darned

This sounds great, but how do you use a variable instead of "MainDb"? – Purvis

@Purvis database is the variable in this example. – Darned

I think that @Purvis 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. – Meltage

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.

Colonel

> 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. – Seligman

@Seligman 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? – Colonel

@Seligman 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... – Colonel

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. – Seligman

@Seligman 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. – Colonel

We have numerous instances of a database with the exact same schema and in this particular case the user is running an SSRS report, and they choose which database from a dropdown. I could distribute the procs across all databases, but I don't want to, and I shouldn't have to - accessing a specific database at runtime in any other language other than TSQL is common, and easy. – Seligman

@Seligman "Don't want to" doesn't support a very strong position. "The easy way to code this is to load stored procedures into each database. But I don't want to! Selecting the database shouldn't be done by the client, even though that works—no, there has to be a database that can be a kind of multi-database that lives in front of all the other databases. It's like, instead of pushing the button to select the floor we want, we type the button number into a device and it pushes the right button for us." Makes sense to me. – Colonel

Yes there is one database that is the master over all of this. I simply don't understand your reluctance to the idea of greater flexibility, we have thousands of developers all over the world writing shitty dynamic sql not because they want to, but because they have to. You wouldn't be a DBA by trade would you, because you sure seem to think like one. – Seligman

@Seligman I'm not a DBA. I have written my own quantities of dynamic SQL. But time and again, when people think they have to use dynamic SQL, there is actually a way to avoid it that is better. It's the voice of experience talking, not elitism or idealism or "resistance to greater flexibility". A whiteboard is infinitely flexible, but no one thinks it's a good database system. – Colonel

If a database datatype existed, it would be used extremely frequently (therefore, it is useful), people wouldn't need to use dynamic sql or distribute stored procs to all database instances, and the end code would be sound. I'm not seeing any downside. – Seligman

@Seligman you have to deploy your tables, and your data, to all instances. Why is it so hard to deploy your stored procedures, too? Sounds like a tooling and deployment problem that you're trying to solve suboptimally (with dynamic SQL) instead of just developing the right tooling. – Colonel

I'm not saying it's "wrong" or "so hard", I would just prefer to do it in an alternative way. Are you of the opinion that there should be one and only one way to do something? Or, if something is "already possible" (regardless of how difficult or dangerous) then the job is done? Don't provide alternative approaches, don't constantly improve things? What a sad place the world would be if everyone shared that philosophy. – Seligman

@Seligman Now you're putting words in my mouth, showing you don't understand my position or you are being dishonest. Of course there isn't only one way to do things. Of course there are alternative approaches, and of course we should constantly improve things. However, building systems that require dynamic SQL is in my experience almost never actually improving things. It seems great, but down the road you'll regret it. Please stop making straw man arguments, where you set up a position that isn't mine, and then easily knock it down! This isn't as black and white as you imply. – Colonel

I didn't put words in your mouth, your position is "distribute your SP's to all the databases", and since that will in fact work, end of discussion. I don't disagree with the viability of that approach, I am simply stating that in some scenarios (mine) there are alternative ways that one may prefer to implement this. – Seligman

There are currently two ways to do this (distribute SP's, use dynamic SQL) - my assertion is that there should be a 3rd way: variables of type DATABASE than can be instantiated at runtime. No, this language enhancement isn't necessary (we have done without it for a couple decades), but it would make implementing certain things far nicer. – Seligman

@Seligman I just ran across this answer of mine from a year ago. Made me laugh and think of this discussion. – Colonel

Yes, and you know how easy (and safer) this sort of thing is in c# (or any other language). Postgres seems to suffer the same shortcoming? So what is it with databases, why don't they have a database or connection variable like other languages? – Seligman

@Seligman I think you're misunderstanding what a database is for. It is a specific abstraction layer for data—tables—on which you place other abstraction layers with a different purpose. You're asking why the DB abstraction later doesn't itself abstract databases. Well, that's not what it's for! SQL is a 4th-generation language, not a 3rd-generation one, and is simply not suited to what you're asking for. – Colonel

"that's not what it's for!" - who declares "what it's for"? Users want this capability, there is good reason for it, they pay for the product....so what is the legitimate, logical reason (not an unsubstantiated "expert opinion") on why we cannot do this? – Seligman

@Seligman You're complaining that the database should be like a full-on application software development language. I'm basically saying, that's not what it is and not what it should be. I don't want this capability, nor do I think there's a good reason for it. What is the legitimate, logical and objective basis for needing a "database object"? I think it's a silly idea. – Colonel

Because it would be useful, there are millions of lines of dynamic sql written because people want this functionality. You wouldn't be forced to use it if you prefer to choose alternative implementations. – Seligman

@Seligman There's a perfectly good way to compose SQL. In an application development language like C#. You might "prefer" that your toothbrush is also good for scrubbing the toilet, but that doesn't mean it's a good idea. – Colonel

Ok, so from SSRS how do I do it without distributing my stored procedures and without writing dynamic sql? – Seligman

@Seligman You don't, just like you don't put diesel in a gasoline engine. Distribute your stored procedures. "How do I X but it has to be my way, Y" is a classic type of question that people continually ask, and the answer is often: you don't. Just do it the best practice way. – Colonel

I'm not saying it has to be my way (actually, you're the one saying that), I'm saying I would like a 3rd alternative, you are saying that because there is already "a" way to do something, that no further approaches should ever be provided. Think about where that kind of thinking leads. – Seligman

@Seligman In this particular case, that kind of thinking leads to avoiding dynamic SQL, and distributing your SP to multiple DBs. So horrible! Use TeamCity and RedGate SQL compare to get change scripts, then use Octopus to deploy them. Or any other set of technology that you like. Dynamic SQL is hard to maintain, hard to understand, and unnecessary. You remind me of the Knights of Ni in MPatHG, who get all excited about adding things to their requirements. "A path! A path! And a shrubbery!" – Colonel

Let's not be disingenuous, if you can't have a discussion on the merits of your side without resorting to mocking your opponent personally it's sad. I'm not saying it's overly horrible, I'm just saying it shouldn't be necessary. Also, where I'm currently at, distributing stored procedures is not allowed, I must use dynamic sql. I'm surrounded by unreasonableness on both sides. – Seligman

@Seligman This is the first that you've said anything about external constraints. I don't need to argue any more. Best of luck to you. P.S. Disingenuous means dishonest. Definitely not that happening here. – Colonel

"You remind me of the Knights of Ni in MPatHG, who get all excited about adding things to their requirements" - yes, that's a perfectly honest assessment of my thought process. – Seligman

I have to apologize for the Knights of Ni comment. It was too much. Sorry about that. – Colonel

lol, no problem man, geez that was a long time ago, but thanks for the apology!! – Seligman

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

Airglow

Answer 5

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

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

Perren

This only sets the context for the dynamic sql statement, not the outer scope. – Teide

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. – Perren

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

Osugi

This is a duplicate of my answer, which was provided 6 years earlier. – Colonel

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

Trow

Answer 8

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;

Fess