SQLSERVERLEARNER

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

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:

Example query:
[sql]
exec sp_MSforeachdb ‘select ”?”’
[/sql]

The above query will list all the database names.

[sql]
exec sp_MSforeachdb ‘select DB_NAME()’
[/sql]

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.

[sql]
exec sp_MSforeachdb ‘select ”?” as databasename,count(*) from [?].sys.tables’
[/sql]

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 ?.

[sql]
exec sp_MSforeachdb ‘select ”&”’,’&’
[/sql]

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.

[sql]
exec sp_MSforeachdb ‘select ”&”’,’&’,’select ”Command2&”’,’select ”command3&”’
[/sql]

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)

[sql]
exec sp_MSforeachdb ‘select ”?”’,’?’,’select ”command2?”’,’select ”command3?”’,’select ”start”’,’select ”end”’
[/sql]

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.