sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable is one of the undocumented stored procedure in Microsoft SQL-Server 2005. It has an amazing facility of auto iteration. It means that it will run T-SQL command for each and every single table of your database. As other system SP, you can find this SP in MASTER DB. If you are a member of sysadmin then you can use this SP very beautifully for your task.
Let’s look at its example:
Note: Create one dummy database and use this example in it only as it will take effect throughout the database and will affect all tables resides in that database.
USE DummyDB
GO
EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
Above query will disable all the constraints of all tables in DummyDB. “?” In above query will be replaced automatically by table name internally and will iterate the loop for all table automatically.
How easy is this??????
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.”

Comments are closed.