In SQL server is there any way to get the 'use database' command to accept a variable

Question

I would like to write a 'generic' script to operate on a number of databases. The 'use databasename' commands will be distributed in the body of the script. I would rather show the database name information at the top of the script. So ideally:

declare @db varchar(100)
set @db = 'data_in'
use @db

This fails. So perhaps

declare @db varchar(100)
set @db = 'data_in'
exec ('use '+@db)

This executes, but presumably changes the database only in the context of the query in the string.
So is there any other way without placing the whole script into a string and executing it that way ie. a cure worse than the disease?

Answer 1

Check your presumption....

Answer 2

You could NOT put a use statement in your script, and supply the database with the osql command (or whatever you're using) like this:

osql -S servername -d databasename -U username -P password -i script.sql

Answer 3

Check out Scripting Variables in SQLCMD.

This enables you to put variables into your scripts in the form:

USE $(db1_name)
...some code...

USE $(db2_name)
...some code...

And have the variable values interpolated from environment variables, parameters provided at runtime, or hard coded value assignments.

Answer 4

I think your presumption’s right; I had the same problem yesterday. I solved it by putting the commands into a single string. Why don’t you like that solution?

Answer 5

Ed already mentioned SQLCMD, a very good choice for scripting.

If that doesn't do it for you, and you have sa rights on the server, and you don't mind the risk of using undocumented features and modifying the master database, you might look into user-defined system stored procedures.

A user-defined system stored procedure (UDSSP) is created in the master database, prefixed with "sp_", and marked as a system object with the undocumented system proc sp_MS_marksystemobject (SQL2005).

It takes its database context from the current connection, or a three-part name if so called.

Sample invocation:

declare @db sysname
declare @sql nvarchar(max)
set @db = 'yourdatabase'
set @sql = 'exec '+quotename(@db)+'..sp_@yourproc'
exec (@sql)

Notes:

1. If you go this route, I strongly suggest using a unique prefix that sorts toward the top, like sp_@yourproc, rather than sp_yourproc, so you can find them again later, and other people know that they are something special.

2. Once the procedure is marked as system, it can't be updated. To make changes, you must drop, recreate and remark as system.

3. Don't do this unless you know what you are doing and have done a little more research. Don't do this if you are risk-averse. Don't do this if you don't have a development instance to test on first.

4. Backup the UDSSPs to file or CSV. A server upgrade can wipe them out.