This blog entry brought to you by the procedure "sp_MSForEachDB"

Posted by & filed under , , , .

My undocumented stored procedure of the day is sp_MSForEachDB. This procedure allows SQL statments to loop through all database names on a server with a dynamic SQL string. The procedure accepts the variable @command1 for the dynamic SQL string, and the database name is passed into the string through the use of "?". Unlike using the sp_ExecuteSQL you are able build the SQL string

One thing to note with this stored procedure is that is does not switch contexts while it executes. So all of the SQL statements will run in the context of the master database. Getting around this issue is fairly trivial using either three-part naming of database objects or through the USE command.

For example executing sp_HelpDB for each database can be achieved through three-part naming:

sp_MSForEachDB ‘?..sp_HelpDB’

or the USE command

sp_MSForEachDB ‘Use ? exec sp_HelpDB’

The sp_HelpDB can be quite useful, I recently ran a script similar to the one below to get a list of IDs that appeared in a table in all of our client databases.

Create Table #Foo
(
DBName sysname,
FooType varchar(20),
FooID varchar(20)
)
Exec sp_MSForEachDB ‘Use ? If Object_ID(”Foo”) Is NOT NULL Insert Into #Foo Select ”?”, FooType, FooID From Foo’
Select * From #Foo

Hope this helps the many (or few rather) people that read this.