SQL Server and the undocumented sp_MSforeachtable procedure

I think that many of you have some custom T-SQL scripts to manage your personal tasks on your databases and I think that many times you've written a procedure that loops through all the database tables in order to perform a task (for example for index optimizations etc.).

Joe Webb has turn on a light on me : SQL Server has an undocumented T-SQL stored procedure called sp_MSforeachtable 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.

The Joe's samples gives you an idea of the usage:

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 ([?])';

How many rows of code if using a cursor for the same task?

The following script in this new sample reports the space used and allocated for every table in the database:

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

So easy and so quick to use... thanks Joe for the tip.

Technorati Tag:

Print | posted on Saturday, October 27, 2007 12:08 PM