SQL Stored Procedure to Run Single Command on All Tables

There is an undocumented stored procedure that you can use to run a single command on all tables in a database. It is very useful when doing routine jobs on all the tables. For example, re-indexing the tables would be done like this:

 sp_MSforeachtable [@command1 =] 'sql_command'    [, [@replacechar =] 'replacement_character']    [, [@command2 =] 'sql_command']    [, [@command3 =] 'sql_command']    [, [@whereand =] 'sql_command']    [, [@precommand =] 'sql_command']    [, [@postcommand =] 'sql_command']  Typical Call  sp_Msforeachtable "DBCC DBREINDEX ('?')",     @precommand = 'print ''Indexing ...''',   @postcommand = 'print ''Completed ...'''

Enjoy and use this with care. It will do any command on all the tables.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: