join the MSSQLTips community

Today's Site Sponsor


 

Find performance issues related to Analysis Services memory limits.
 


Run The Same SQL Command Against All SQL Server Databases
Written By: Tim Ford -- 1/17/2008 -- 8 comments -- printer friendly -- become a member



Speed up SQL script deployment

        Win SQL Books  -----  SharePoint Tips  -----  Live Webcast - SQL Backup Mistakes  -----  Bookmark and Share        

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!


  Wasting time running multiple scripts against multiple SQL Servers manually?
Execute multiple scripts against multiple SQL Servers with a single click

Try SQL Multi Script and execute all those scripts with just one mouse click.

Download your free 14-day trial now!

"Just tried SQL Multi Script and very impressed with it. Talk about reducing work load!"
Neil Abrahams SQL Server
DBA/Developer

 
SQL Multi Script logo
Red Gate Software - ingeniously simple tools

 

 



Idera - SQL safe backup

Need more space for your backups? Idera has announced the latest edition of SQL safe, their award-winning backup and recovery solution. SQL safe v6.0 includes brand new compression algorithms developed with some of the leading compression experts in the world. Plus, SQL safe 6.0 continuously optimizes compression to give you the fastest possible backup with the highest possible compression, every time you do a backup.

Download now!

More SQL Server Tools
SQL Compare

SQL comparison toolset

SQL Backup

SQL Refactor

SQL Data Generator


Sponsor Information
Free SQL Server performance monitoring dashboard – Idera SQL check

Quickly and accurately deploy database changes with Red Gate's SQL Compare – the industry standard comparison and deployment tool.

You don't know, what you don't know about SQL Server... Customized Consulting and Training

CaeerQandA.com – Shed some light on your future

Webcast - Top 10 SQL Server Backup Mistakes and How to Avoid Them

All SQL Server, all the time! Sign-up for the MSSQLTips newsletter!

Interested in SharePoint? Love the tips? Check this out...

Free whitepaper - Managing Complex Database Changes



Copyright (c) 2006-2010 Edgewood Solutions, LLC All rights reserved
privacy statement | disclaimer | copyright | advertise | write for mssqltips | feedback | about
Some names and products listed are the registered trademarks of their respective owners.