SQL Server – sp_MSforeachtable – Undocumented Stored Procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data.

No doubt we can use the dynamic statement or cursor for this purpose.

But we have better alternative way; this is an undocumented stored procedure called as “sp_MSforeachtable” in the master database. This stored procedure will loop through all the tables in the database for performing a command. This stored procedure accepts the following input parameters.

 

Example: 

 

Disable all constraints 

sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

 

Similarly enable all constraints 

sp_msforeachtable “ALTER TABLE ? CHECK CONSTRAINT all”

 

Disable all Triggers

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER  all”

 

Similarly enable all Triggers

sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER  all”

 

Checks the integrity of each table in the specific database using the DBCC CHECKTABLE command

sp_msforeachtable “dbcc checktable ('?')”