Sunday, March 10, 2013

MSSQL: sp_MSforeachtable to loop through all tables without using cursors

sp_MSforeachtable is an undocumented stored procedure since SQL 2005 that can come in real handy, looping through all tables in a database to get information without having to write cursors. The idea of its usage is to use the [?] placeholder and SQL will intelligently do a replace with a table name.

Just some sample usage of sp_MSforeachtable.

--Check space used for each table in a database
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]';

--Check for integrity of tables in a database
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])'

Undocumented may mean it may not be supported in future versions. My last check is sp_MSforeachtable still works for SQL 2008.

Related reading:
  1. MSSQL: sp_MSforeachdb to loop through all databases without using cursors

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.