Tuesday, August 18, 2009

SQL Commands - Disable Constraints

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.

No comments: