sp_MSforeachdb – Understanding and Using – InDetail

Posted on : 16-12-2011 | By : Devi Prasad | In : SQL Server Basics

Share:

1


The System stored procedure sp_MSforeachdb is not listed in MSDN.

Following is the indept analysis on this procedure.
This procedure is used to run a command/query using all the databases on the server.
? is used to indicate the database name.
The query passed to this procedure is executed n number of times, where n is the number of databases on the server.

Name: sp_MSforeachdb
Owner: sys
Type: stored procedure

Input Parameters:

@command1 nvarchar(4000)
@@replacechar nchar(2)
@@command2 nvarchar(4000)
@@command3 nvarchar(4000)
@@precommand nvarchar(4000)
@@postcommand nvarchar(4000)

Example query:

exec sp_MSforeachdb 'select ''?'''

The above query will list all the database names.

exec sp_MSforeachdb 'select DB_NAME()'

The above query will return the same database name but for n number of times, where n is the number of databases.

we can come up with multiple uses of this procedure.
Use ? where ever you need to use the database name.

exec sp_MSforeachdb 'select ''?'' as databasename,count(*) from [?].sys.tables'

The above query will give the number of tables in each database.

@replacechar is the second input parameter which specifies the charecter that has to be replaced by the database name. Default value for this parameter is ?.

exec sp_MSforeachdb 'select ''&''','&'

In the above query & is used instead of ?.

The next parameters @command2 and @command3 are the next set of commands that can be run for each database name.

exec sp_MSforeachdb 'select ''&''','&','select ''Command2&''','select ''command3&'''

The above query runs @command1, @command2 and @command3 in the same order for each database name.

@precommand is the command that is executed first(before starting process for the first database)
@postcommand is the command that is executed last(after completing process for the last database)

exec sp_MSforeachdb 'select ''?''','?','select ''command2?''','select ''command3?''','select ''start''','select ''end'''

The above query executes the following:
– executes @precommand
– executes @command1, @command2 and @command3 in the same order for each database name
– executes @postcommand

Further sp_MSforeachtable is a similar procedure that is used for executing a query on each table.

(Visited 479 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (1)

Can you show me how to use this to return a list of db_users for each database? I would like to combine this sp with the query SELECT name, type, type_desc
FROM sys.database_principals as I am doing an inventory of users. Thank you.

Write a comment



Tags: , , ,