The following commands are from this URL
http://gchandra.wordpress.com/2008/02/18/sql-server-clean-your-database-records-and-reset-identity-columns-the-shortest-path/
--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'
--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
--Reset Identity on tables with identity column
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
I found the commands are very useful. For example, I once got the foreign key constraint error when trying to import data into my database. I executed the first two commands to disable constraints and triggers first, then imported the data, the errors were gone. After importing data, I then executed the last three commands to re-enable and reset stuff.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment