As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data; or perhaps we need to run DBCC CHECKTABLE for every table in our database to ensure the integrity of all pages and structures that make up the tables.
We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck!
Fortunately, there's a better way. It's the undocumented sp_MSforeachtable stored procedure in the master database. It's like a cursor in that it loops through each table in the current database and executes a script that you define. But it requires considerably less code.
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.
Cheers!
Joe
