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:
SQL Server