Thursday, May 31, 2012

For Each Table loop in SQL Server

SQL Server has a stored proc called sp_MSforeachtable that allows you to easily run a query on each table.
 
 For example:

exec sp_MSforeachtable 'DELETE FROM ?';

This deletes all data from all tables in the database. The ? = the table. So if you wanted to select everything from every table it would be exec sp_MSforeachtable 'SELECT * FROM ?';

Reference
1. http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
There was an error in this gadget