The undocumented sp_MSforeachtable procedure

The undocumented sp_MSforeachtable procedure


For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

That's a whole lot more palatable than developing a cursor-based solution!

Here's another example. The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';

So, the next time you need to loop through each table, give the sp_MSforeachtable procedure a try.