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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: