Run The Same SQL Command Against All SQL Server Databases
Written By: Tim Ford -- 1/17/2008 -- 8 comments
-- printer friendly --
become a member
Problem
There are times when I find myself needing to run a SQL command against each database on one of my SQL Server instances. There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb.
Solution
The syntax for this undocumented procedure is:
EXEC sp_MSforeachdb @command
(Where @command is a variable-length string.)
Example 1: Query Information From All Databases On A SQL Instance
--This query will return a listing of all tables in all databases on a SQL instance:
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
EXEC sp_MSforeachdb @command |
You can alternately omit the process of declaring and setting the @command variable. The T-SQL command below behaves identically to the one above and is condensed to a single line of code:
--This query will return a listing of all tables in all databases on a SQL instance:
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' |
Example 2: Execute A DDL Query Against All User Databases On A SQL Instance
| --This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification date
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'
EXEC sp_MSforeachdb @command |
As you may notice, there are additional items to take into consideration when limiting the scope of the sp_MSforeachdb stored procedure, particularly when creating or modifying objects. You must also set the code to execute if the IF statement is true by using the T-SQL keywords BEGIN and END. You should take note that the USE ? statement is contained within the BEGIN...END block. It is important to remember key T-SQL rules and account for them. In this case the rule that when creating a procedure, the CREATE PROCEDURE phrase must be the first line of code to be executed. To accomplish this you can encapsulate the CREATE PROCEDURE code within an explicit EXEC() function.
What about the "?" Placeholder
Throughout the examples provided above you'll see the use of the question mark as a placeholder for the database/database name. To reference the database name as a string to be returned in a query, embed it between a double set of single quotation marks. To treat it as a reference to the database object simply use it by itself (as presented in Example 3b.) It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database, for each database in your SQL instance. If you have 5 databases hosted in the current instance and you were to run the stored procedure code above while in the context of DBx it would execute the T-SQL text of the @command 5 times in DBx. This behavior is evident in the output of Example 3 below.
Example 3: Query File Information From All Databases On A SQL Instance
--This query will return a listing of all files in all databases on a SQL instance:
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF' |
What happens though if we omit the USE ? clause, which sets the scope of the query? As you can see below, though it is apparent the code executed for each database, it never changed context. Pay particular interest to the filename column and you will see that the query executed from within the context of the master database (where I ran the query from) for each database in the SQL instance (as noted by the database name being returned via the use of the "?" placeholder).
| --Remove the USE ? clause and you end up executing the query repetitively within the context of the current database:
EXEC sp_MSforeachdb 'SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF' |
Why Not Just Use a Cursor?
Sure, a cursor can accomplish all that I've presented above, but let's look at the code required to set up a cursor to execute the command used in Example 3:
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor |
Considering the behavior is similar I'd rather type and execute a single line of T-SQL code versus sixteen.
Next Steps
- sp_MSforeachdb is extremely useful for pulling together metadata about your various SQL databases. I use it quite frequently for reporting on such important metrics as database file sizes, amount of free space, and backup status.
- In future tips I will present how to collect those metrics and many more, and report on them via SQL Server Reporting Services. Stay Tuned!
Readers Who Read This Tip Also Read
Comment or Ask Questions About This Tip
 Twitter This Tip!
|
