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