SP_MSFOREACHTABLE SYSTEM STORED PROCEDURE

If you wanted to execute a table row count against every table in your

database, you may first be inclined to write a cursor that queries the

informationschema views or system tables to first get a list of tables

in the database. Then process each row with the required count(*)

command.

The sp_MSforeachtable stored procedure can do all of this for you

behind the scenes. All you do is pass in the commands you want executed

and sp_MSforeachtable does the rest. Know that the question mark (?) is

the table place holder for this procedure. Anywhere you would like to

refer to the table, use the question mark identifier. You can pass in

as many as three separate commands by identifying each command as

@command1, @command2, and @command3.

Consider the following scenario:

sp_MSforeachtable @command1 = "PRINT REPLICATE('*',DATALENGTH('?'))",

@command2 = "SELECT '?' = COUNT(*) FROM ?"

The above statement will output similar to the following:

**************

[dbo].[Table1]

--------------

309

***************

[dbo].[Table29]

---------------

545

*********************

[dbo].[HistoryTable1]

---------------------

968

You can use sp_MSforeachtable most anywhere. Note that this procedure

resides in the master database.