SYSK 57: Handy Undocumented Stored Procs

  • Article

In case you didn’t know, there are a couple of very cool SQL stored process that allow you to execute a T-SQL statement for each table and for each database.  They are sp_MSforeachdb and sp_MSforeachtable.

For example, executing
     sp_msforeachtable 'sp_spaceused "?"'
will result in the dump information on space occupied by each table.

Running
     EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
Will check for allocation and consistency errors

For more information on these two stored procs, and on more advanced examples, including pre- and post-execution commands, check out http://www.databasejournal.com/features/mssql/article.php/3441031.