Raymond Lewallen : Using sp_MSforeachtable
"...sp_MSforeachtable will loop through all the tables in the database, performing a command. In our case above, we want to run 2 commands: 1 to set NOCHECK on all CONSTRAINTs, and another to DISABLE all TRIGGERs.
sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
The '?' serves as a place holder for the table name. Once your data is loaded, just turn everything back on:
sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
You can be a bit fancier and run more than 1 command per call to sp_msforeachtable (limited to 3 commands) by passing in parameters of commands you want to execute per table.
sp_msforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT all', @command2='ALTER TABLE ? ENABLE TRIGGER all'
Another great use for this sp is for DBCC commands on tables or database, as there is also a sp_MSforeachdb stored procedure too.
sp_msforeachtable 'dbcc checktable ('?')'"
Cool...
I guess I'll have to rework my procedures that use a cursor against the information_schema.tables view... :|
No comments:
Post a Comment
NOTE: Anonymous Commenting has been turned off for a while... The comment spammers are just killing me...
ALL comments are moderated. I will review every comment before it will appear on the blog.
Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...
I reserve, and will use, the right to not approve ANY comment for ANY reason. I will not usually, but if it's off topic, spam (or even close to spam-like), inflammatory, mean, etc, etc, well... then...
Please see my comment policy for more information if you are interested.
Thanks,
Greg
PS. I am proactively moderating comments. Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...