Undocumented sp_msforeachtable, sp_msforeachdb procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database or on all the databases within a instance. Microsoft SQL Server provides two undocumented stored procedures designed for iteration that allow you to process through all tables in a database, or all databases in a SQL Server instance. The first stored procedure, "sp_msforeachtable" allows you to easily process some code against every table in a single database. The other stored procedure, "sp_msforeachdb" will execute a T-SQL statement against every database associated with the current SQL Server instance.

 

For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.

USE AdventureWorks;
EXECUTE sp_msforeachtable 'DBCC CHECKTABLE ([?])';

 

As another example, you can sp_msforeachdb procedure to find all the stored procedures in all available databases on the instance which have the word 'RPT' in their definition by running the following command.

EXECUTE sp_msforeachdb 'SELECT ''?'' AS DB, SPECIFIC_NAME, OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) FROM [?].INFORMATION_SCHEMA.ROUTINES WHERE OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) LIKE ''%RPT%''';

Some level of testing and care should be taken when using undocumented code from Microsoft. Since these stored procedures are not documented, it means that Microsoft might change this code with any new release or patch without notifying customers.