solving sql server problems for millions of dbas and developers since 2006

Run same command on all SQL Server databases without cursors

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!

Related Tips: 1 | 2 | 3 | More | Become a paid author

Last Update: 1/17/2008

Share:

Free SQL Server Learning

Comments and Feedback:

Thursday, January 17, 2008 - 5:23:02 PM - probaby

Read The Tip

Thanks for your wonderful scripts!

You save my time and money!

Have a great day!

Thursday, April 03, 2008 - 11:53:47 AM - prasanthg74	Read The Tip
nice script ! thanks a bunch!

Friday, April 04, 2008 - 7:07:46 AM - admin

Read The Tip

Thanks so much.

Tim Ford did a great job with this tip.

Thank you,
The MSSQLTips.com Team

Wednesday, December 10, 2008 - 3:23:23 PM - robvon

Read The Tip

You may need some "[" and "]" around the '?' to deal with dodgy database names.......

as in:

EXEC sp_MSforeachdb 'USE [?] SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'

Sunday, December 14, 2008 - 9:04:32 PM - timmer26	Read The Tip
You are absolutely correct. This is particularly important when dealing with spaces in database names.

Friday, November 20, 2009 - 3:49:19 AM - bobmihada

Read The Tip

--// Just another flavor to offer IF OBJECT_ID('tempdb..##TempDBInfo') IS NOT NULL BEGIN PRINT 'Previous ##TmpDBInfo found and Dropped.' DROP TABLE ##TempDBInfo END CREATE TABLE ##TempDBInfo(DBName nVarChar(255), FileName nvarchar(255), fileSize INT) EXEC sp_MSforeachdb 'USE [?] INSERT INTO ##TempDBInfo SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF' SELECT * FROM ##TempDBInfo --// DROP TABLE ##TempDBInfo

Wednesday, January 27, 2010 - 11:19:23 AM - Akbar

Read The Tip

Hi All, Is there are way to grant Execute permission to a particular Stored procedure or Function in all database of SQL Server 2008 Instance? The below one use to Grant Execute permission to any procedure.

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'

But when trying the below one I'm getting error

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE ON [?].[dbo].[sp_StoredProc1] TO UserOrRoleName'

Error Message: Cannot find the object 'sp_StoredProc1', because it does not exist or you do not have permission.

I have the 'sp_StroedProc1' exist in all my database in the sql server 2008 instance. Any help will be highly appreciated.

Friday, January 29, 2010 - 12:02:55 PM - aprato	Read The Tip
EXEC sp_msforeachdb 'USE ?; if object_id(''sp_StoredProc1'') is not null begin print ''?'' GRANT EXECUTE ON [dbo].[sp_StoredProc1] TO public end'

Monday, April 26, 2010 - 3:02:40 PM - thomashmaine

Read The Tip

is there a way to have a variabled declared withing the statement?

select @command1 = 'use [?]; exec sp_grantlogin [@user2]'

print @user2

exec sp_MSforeachdb @command1

Wednesday, November 03, 2010 - 10:11:28 AM - Pradip Patel

Read The Tip

You need to replace

SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
with

SELECT @Command = 'USE '+ @DB_NAME + ' SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'

when you want to execute by cursor otherwise it gives you all file name as master.mdf

Tuesday, February 01, 2011 - 11:49:50 AM - Larrybud

Read The Tip

There are times when you might need a cursor:

1) running a script against certain databases, instead of all

2) wanting your script results in one table (for a select, for example)

Thursday, September 08, 2011 - 4:16:37 PM - Jason

Read The Tip

When you need a special condition of list of DBs such as not offline, not read-only, use temp table or CTE or table variable, for example, the code below

Regards,

Jason

http://usa.redirectme.net

-----------------

SET NOCOUNT ON

DECLARE @dbName varchar(255), @rc int

SELECT name
INTO #db
FROM sys.databases
WHERE
    name NOT IN ('master', 'model', 'msdb', 'tempdb', 'admindb') AND
    name NOT LIKE '%ReportServer%' AND
    DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
    DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'

SELECT @rc = 1, @dbName = MIN(name)
FROM #db

WHILE @rc <> 0
BEGIN

EXEC DB.schema.DO_SOMETHING

    SELECT TOP 1 @dbName = name
    FROM #db
    WHERE name > @dbName
    ORDER BY name

SET @rc = @@ROWCOUNT
END

DROP TABLE #db

Tuesday, February 07, 2012 - 8:49:37 AM - Fernando Paez Becker

Read The Tip

There is anothers parameters to comments... EXEC sp_msforeachdb sintax accepts

EXEC sp_msforeachdb @precommand='---YOUR TEXT FOR GENERAL PURPUSE HERE-TEMP TABLE CREATION IE...

CREATE TABLE ##TEMP1 (ID INT,NAME VARCHAR(15);SELECT * INTO ##TEMP2 FROM ##TEMP1',

@COMMAND1='USE [?];SELECT * INTO ##TEMP1 WHERE ...',

@COMMAND2='USE [?];SELECT * INTO ##TEMP2 WHERE ...',

@COMMAND3='USE [?];SELECT * INTO ##TEMP1 WHERE ...',

@POSTCOMMAND='SELECT * FROM ##TEMP1 UNION SELECT * FROM ##TEMP2;DROP TABLE ##TEMP1;DROP TABLE ##TEMP2;'

Tuesday, February 28, 2012 - 4:31:02 AM - akash

Read The Tip

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name FROM master..sysdatabases where name like '%_Se'

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Command = 'USE ['+ @DB_NAME + '] INSERT INTO genMenuMaster
                    (MenuId, ModuleId, MenuName, MenuType, ParentId, MenuURL, [Position])
VALUES     (1163, 32, ''SMS Mobile DND Status Report'', 0, ''General/SMSMobileDNDStatus.aspx'', 60)'
   EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Tuesday, February 28, 2012 - 4:31:43 AM - akash	Read The Tip
Server: Msg 105, Level 15, State 1, Line 3 Unclosed quotation mark before the character string '\General/S'.

Tuesday, March 13, 2012 - 11:35:27 PM - surajkuamwat

Read The Tip

Respect sir

i am facing some problem in sql query using cursor declaration t was try to do with declare variable

declare

cmain cursorforselect*from gstock

open

cmain

how to use cursor in fetch status...

Tuesday, March 13, 2012 - 11:54:24 PM - SURAJ

Read The Tip

Dear sir

I AM FACING SOME PROBLEM IN SQL QUERY CURSOR DECLARATION I WANT TO USE CURSOR WITHOUT DECLARE VARIABLE

PLEASE GIVE ME SOLUCTION

MENTON MY QUERY

DECLARE @ABCTABLE AS TABLE (STKCODE INT,STOCKNO VARCHAR(20))

declare abc_cursor cursor for select stkcode ,stockno from stock

OPEN abc_curosr ** THIS QUERY EXECUTE SUCESSFUL BUT HOW TO USE IN FETCH NEXT FROM CURSOR

FETCH NEXT FROM abc_curosr INTO @stkcode,@stockno ** ERROR CURSOR VARIABLE NOT READ

WHILE @@FETCH_STATUS = 0
BEGIN

INSET INTO @ABCTABLE (STKCODE,STOCKNO) VALUES (@stkcode,@stockno)

FETCH NEXT FROM abc_curosr INTO @DB_Name
END

CLOSE abc_curosr
DEALLOCATE abc_curosr

Tuesday, May 01, 2012 - 6:27:28 PM - corey_lawson

Read The Tip

This, and sp_msforeachtable, are internally not magic or undocumented, in that they don't invoke any CLR functionality, extended stored procs, obfuscated TSQL, etc.

They're just plain ol' TSQL procs that executes the code specified in the parameters inside a CURSOR that loops over the desired objects...

Wednesday, May 16, 2012 - 6:40:50 AM - Sandeep Maher

Read The Tip

Hi,

I got your email id in forum.
I am having issues in SQL,
Issues is that I am having more than 50 customers and there is more than 50 Database in SQl for every customers.
Now All SP's And Functions are same but now problem is in maintenance, If I modify any SP then I need to modify SP manually in all database.
So is there any way to avoid such maintenance problem So I can modify one SP and that will get update in all remaining Database?

Or Can I get script in c# code using the can i modify SP in all database?
EXEC sp_MSforeachdb can not work for me because some of my SP text length is more than 10,000.

Kindly help me.. Thanks in Advance.

New SQL Monitor v3.0

Try it live

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!"

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Join the over million SQL Server Professionals who get their issues resolved daily.

Are you waiting on SQL Server? Learn about these DMV's.