SQL 2005+

Executing a Command Against Every Table in a Database

by Richard Carr, published at http://www.blackwasp.co.uk/SQLForEachTable.aspx

When managing a database it is common to need to execute the same function against all of the tables. This might be to perform a series of database checks for each table or perhaps to disable all triggers and constraints whilst making changes to the data.

sp_MSforeachtable

You will occasionally find that you have a task to perform for every table in a SQL Server database. For example, you may want to perform checks against the tables, rebuild the indexes or generate statistics for your tables. You may be preparing for major data updates by temporarily disabling all of the triggers, foreign key relationships and check constraints. Performing the task manually for each table would be slow and prone to errors.

If you wish to execute the same command for all of the tables in your database you could read the table data from the master database and use this information as the basis for a cursor. This is a good approach when the procedure is to be reused, particularly if it will be included in production code. However, for quick tasks it can be cumbersome.

If you are performing a one-off task, an alternative is provided by the sp_MSforeachtable stored procedure. You supply this procedure with a command, which it executes against every table in the current database. You provide the command as an NVARCHAR value with a question mark (?) in each place where a table name should be substituted. The only drawback to the stored procedure is that it is undocumented. This means that it could be removed from, or changed in, future versions of SQL Server. You should therefore not include its use in production code.

For a simple example of sp_MSforeachtable's use, try running the following statement. This prints the name of each table. Note the use of the question mark where the table names will be positioned on execution.

EXECUTE sp_MSforeachtable 'PRINT "?"'

You are not limited to a single statement to run for each table. You can pass a batch of commands to the parameter. This is demonstrated in the script below, which disables all of the triggers and constraints in the database.

EXECUTE sp_MSforeachtable '
    ALTER TABLE ? DISABLE TRIGGER ALL
    ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Using Alternative Replacement Characters

If your statement or batch includes a question mark you'll need to change the character that is replaced with the table names. You can pass the new character to the second parameter of the stored procedure. The following example does this to print a list of table names, each followed by a question mark.

EXECUTE sp_MSforeachtable 'PRINT "*?"', '*'