Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Disable triggers and constraints in SQL Server 2005

Modified: 2008/08/15 09:07 by admin - Categorized as: SQL Server


Sometimes you may want to disable triggers and constraints in your SQL Server database, for instance when deleting all contents, or when importing data from flat-file or xml.

You can use these scripts to disable and enable your triggers and constraints: SQL:
-- Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Disable all Triggers exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- execute updates here...

-- Enable all Constraints exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

-- Enable all Triggers exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

You can show the status of your constraints with this script. SQL:
SELECT
    CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END AS STATUS,
    OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
    OBJECT_NAME(FKEYID) AS TABLE_NAME,
    COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
    OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,  COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME 
FROM
    SYSFOREIGNKEYS
ORDER BY
    TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

Pleaese be careful and don't forget to turn them back on.

 © Evident Interactive BV