SQL Server provides an undocumented stored procedure that is handy for applying commands to all of the tables within a catalog, sp_MSforeachtable:
sp_MSforeachtable 'print ''?'''
The question mark will be replaced by the table name. In a similar vein, we can iterate over all databases using sp_MSforeachdb. These procedures could disappear at any time, but they have been present in all versions from 7.0 to 2008R2 so far.
Both procedures accept the following arguments:
- @command1 - the command to run for each table
- @replacechar - the placeholder character for the table name (default '?')
- @command2 - another command to execute for each table
- @command3 - another command to execute for each table
- @precommand - a command to execute before iterating
- @postcommand - a command to execute after iterating