That Coding Guy

Tech Tips and Tales, and Lessons Learned

Run a SQL Server Stored Procedure inside any Database using a Parameterized Database Name

By

Running a SQL Server Stored Procedure in the context of an arbitrary database that is specified at runtime is usually very difficult. By using dynamic SQL, however, you can pass a database name as a parameter to the stored procedure and run the SQL code inside that stored procedure in the context of the specified database. Dynamic SQL gets a bad rap, but in a controlled environment, dynamic SQL is not evil and it can be an invaluable tool.

Details

Every SQL Server DBA has a library of scripts to provide insight into their servers. Most of this information is server-wide or process-level information so the database context in which these scripts run is irrelevant. As a result, these scripts can be compiled into stored procedures and tucked away in a “Utility” database, then run from any database.

However, some scripts need to collect information from an arbitrary database, where the name of the target database changes or needs to be specified at execution time. For example, a generic script to list the space used by every table in a database or to show the number of rows in each table in a database would need to be able to run against any database. Generic scripts like these cannot normally be run as stored procedures because they will only reflect the tables in the database that actually holds the stored procedure — the database in which the stored procedure was compiled.

Working around this problem is usually done in one of two ways:

  • Put a copy of each of these stored procedures into each target database, then execute the copy in that database to get information for that database.
  • For infrequently-used scripts, run the script interactively in SQL Management Studio.

Neither one of these methods is a good solution. Running scripts interactively though SQL Management Studio doesn’t scale. It works when you have a few servers with a few databases or where all your servers can be connected via a single instance of SQL Management Studio, but it fails in a larger or more complex environment. For example, in our environment we were responsible for several hundred servers that were all running in remote client offices and where we could only connect to each server via a remote SSH command line. SQL Management Studio worked fine in the data center but in the field it was just not an option.

Putting copies of the stored procedures into each database is a better alternative, but only because “it stinks less”. It is still relatively horrible for many reasons, including polluting your application and user databases with “utility” procedures, the need to maintain all those procedure versions in all those databases, and the need to populate your utility procedures into every new database you create. Being able to run these types of scripts as single instances of stored procedures in a Utility database is a MUCH better solution.

Enter Dynamic SQL.  (Stop booing and hissing – dynamic SQL really is not _always_ evil.)

Dynamic SQL — a string of SQL that is run by the “EXEC( .. )” TSQL command — will compile and run whatever SQL it is given. That SQL can include a “USE database” statement, thus allowing the SQL code to change to another database before running the remaining SQL code. Running a SQL command in another database is as simple as running something like the following from ANY database context, including from inside a stored procedure that lives anywhere:

EXEC( 'USE MyOtherDatabase SELECT DB_NAME()' )  -- prints "MyOtherDatabase"

 

Using this technique, you can put each of those generic scripts into their own stored procedure, add a parameter to each stored procedure to accept a database name, put one single copy of the stored procedure into your Utility database, then call the stored procedure from anywhere, passing in the name of the database in which you want it to operate.

For example, the following “traditional” example fails (aka: “it behaves as expected”) – the “SELECT DB_NAME()” prints the name of the database that contains the stored procedure rather than the name of the database from which the procedure is being run:

USE utility
GO
CREATE PROCEDURE p_fail_static
AS
SELECT DB_NAME()

USE master
GO
EXEC utility..p_fail_static  -- prints "utility", proving that its context is still the "utility" database rather than "master"

 

However, the following “dynamic” example does what we need. The “SELECT DB_NAME()” in this case prints the name of the database that was passed to the procedure as a parameter, regardless of where the stored procedure lives or from where we run it:

USE utility
GO
CREATE PROCEDURE p_test_dynamic(@dbname VARCHAR(255) = NULL)
AS
DECLARE @sql VARCHAR(MAX)
SET @sql = 'USE ' + @dbname + ' SELECT DB_NAME()'
EXEC (@sql)

USE master
GO
EXEC utility..p_test_dynamic 'model'   -- prints "model"
EXEC utility..p_test_dynamic 'tempdb'  -- prints "tempdb"
EXEC utility..p_test_dynamic 'master'  -- prints "master"

 

Using this technique, we can encapsulate a SQL script inside a stored procedure but at run time specify the database in which we want that script to run. (See this post and this post for real-world examples of this technique.)

Just remember that you ARE still running dynamic SQL so there are additional things to keep in mind such as security considerations, user-context considerations, etc. But as long as you will be executing the stored procedure from an admin-level user account that already has access to the underlying data, and as long as the stored procedure itself is locked down to only allow execution by admin-level user accounts, this is a safe and effective way to solve an otherwise-messy problem.

Note: This technique won’t work on Azure SQL Databases. On Azure SQL Database, the database name in the USE statement can only refer to the current database; the only way to change an Azure SQL Database context to a different database is to connect directly to that other database.