How to execute a T-SQL command to every table in SQL Server database
sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database.
Examples on how to use it:
1-Perform an unconditional reindex over all tables in the database:
EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'2-Truncate all tables in the database:
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
3-Get the information about the number of records from all tables in the database:
EXEC sp_MSforeachtable 'SELECT ''?'' TableName, Count(1) NumRecords FROM ?'
4-Drop all tables in the database:
EXEC sp_MSforeachtable 'DROP TABLE ?'
and many more…
In the next post we will have a look on how to use SP_Msforeachdb for the same but every database in your server.
See you then …
Leave a Reply