Hidden Stored Procedure:Run command for every database

Recently, I needed to run some commands across all the databases on a server. Initially, my strategy was to list all the databases with “SELECT name FROM sysdatabases “ on the “master” db. After talking with a colleague I discovered that SQL Server 2000 has some ready made stored procedures, that allows you to either run a command against all tables in a db or a command across all databases on a server.

The two stored procedures are sp_MSforeachtable and sp_MSforeachdb. Note: Except for the name and that one is for every database and one is for every table, they are the same. To use it to run commands against all databases, just change sp_MSforeachtable to sp_MSforeachdb.

sp_MSforeachtable returns one or more rows for each table with each table defaulting to its own resultset.

Sp_Msforeachdb returns one or more rows for each accessible db, with each db defaulting to its own result set.

------------------------------------------------
Procedure:
Sp_MSforeachdb
@command1
@replacechar = '?'
[,@command2]
[,@command3]
[,@whereand]
[,@precommand]
[,@postcommand]

Purpose:
Execute up to three commands for every accessible db in a server (optionally matching the @whereand clause) @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.

Example:
EXEC sp_MSforeachdb @command1 = 'PRINT "Listing ?=', @command2='SELECT * FROM ?', @whereand=' AND name like "title%"'
--------------------------------------------------------