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 ? WITH CHECK CHECK CONSTRAINT all"


Disable all Triggers

  
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"


Similarly enable all Triggers

sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER  all"




Delete data from all Table

exec sp_MSforeachtable "delete from ?"





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

  
sp_msforeachtable "dbcc checktable ('?')"