There are many reasons for me to blog. One of the less vain is to recover and reuse code I wrote earlier. In my current project we have a need to test, test and retest the initial load of the data warehouse. 
For this purpose I wrote this T-SQL script that:
Hi Marco! Nice write-up. I'm not a SQL server expert but I had some doubt about this method. In particular, I'm afraid that, for some reason, the client might lose it (bug in client, accidental user shutdown, network split etc. etc.) right after the drop constraints but before the recreate constraints statement. In which case you'd have to somehow restore the constraint definitions from backup or modeling tool. Probably not a huge problem but good for some unplanned downtime. So I was wondering about this alternative instead:...
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable "TRUNCATE TABLE ?"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
This way, at least the constraint DDL is preserved at all times.
Thank you Roland,
Good remark/suggestion.
In fact I like your code even better.
(Learning something every day)
I am an experienced Microsoft Business Intelligence Professional and use this blog to share my expertise and experience. More »
 |
 |
