New Post has been published on Varinder Sandhu 's Blog
New Post has been published on http://www.varindersandhu.in/2011/10/16/sql-server-sp_msforeachtable-undocumented-stored-procedure/
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.
sp_MSforeachtable
Example:
<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->
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 ('?')”
