You can find and review the sp_MSForEachTable and sp_MSForEachDb system stored procedures in the master database, but they are not documented in SQL Server Books OnLine. Microsoft has designed them to support writing a single statement that can perform the same activity on all databases on the current server or on all tables in the current database.
To demonstrate this, set Query Analyzer to Result In Text and execute the following:
exec sp_MSforEachDb @Command1 = "Print '? ' ", @Command2 = "select count(name) from ?.dbo.sysobjects where xtype = 'U'"
SQL Server returns a count of user-defined tables from each database on the current server:
Asset5 ------------ 36 DEPLOY ------------- 0 master -------------- 10
The @ Command 1 and @Command2 parameters are used to specify the actions that the stored procedure will execute against each database. The database name was replaced with a question mark. It is possible to specify up to three commands (using @Command3). Behind the scenes, the stored procedure will open a cursor for the records in the sysdatabases table (which contains a list of existing databases) and dynamically assemble a batch that will be executed against each record in a loop. The following command creates a report about space usage of each database:
exec sp_MSforEachDb @Command1 = "use ? exec sp_Spaceused"
It is even more interesting to run sp_spaceused against all tables in the current database:
exec sp_MSforEachTable @Command1 = "sp_spaceused '?'"
Unfortunately, the result is not nicely aligned in either text or grid mode. You can also get a number of records in each table:
exec sp_MSforEachTable @Command1 = "Print '?'" @Command2 = "select Count(*) from ?"
In the last two queries, the result is not ordered as you might expect. It simply follows the order of records in systables (in other words, the order of creation). If you want to order it by table name, you must use the @whereand parameter:
exec sp_MSforEachTable @command1 = "exec sp_spaceused '?'", @whereand = "order by 1"
This parameter was originally designed to allow you to add a Where clause, but since the query is dynamically assembled, you can sneak an Order By clause into it as well.
You can use the @replacechar parameter to specify a different placeholder for database and table names. This parameter is useful when your commands require the use of a question mark—for example, as a wild card in the Like clause.
If a command should be executed only once before or after the loop, you should use the @precommand and @postcommand parameters.