... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2011-11-16

Iterating Over Tables/Databases in SQL Server

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

Blog Archive