USEFUL MS SQL STATEMENT for Triggers and Constraints

------------
-- Disable all triggers and constraints in a database
------------

USE CopyOfAdventureWorks;

-- SQL disable all triggers
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

-- SQL disable all constraints
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- Enable all triggers on a table
ALTER TABLE Production.Product ENABLE TRIGGER ALL

-- Enable all check contraints on a table
ALTER TABLE Production.Product CHECK CONSTRAINT ALL
GO
------------
-- Single constraint disable and enable
------------
USE CopyOfAdventureWorks;
-- SQL disable constraint
ALTER TABLE Production.Product NOCHECK CONSTRAINT CK_Product_DaysToManufacture
GO

-- SQL enable constraint
ALTER TABLE Production.Product CHECK CONSTRAINT CK_Product_DaysToManufacture

GO

-- SQL enable constraint with check of current data
ALTER TABLE Production.Product WITH CHECK
CHECK CONSTRAINT CK_Product_DaysToManufacture
GO

-- SQL enable constraint with no check of current data
ALTER TABLE Production.Product WITH NOCHECK
CHECK CONSTRAINT CK_Product_DaysToManufacture

-- Check integrity of all constraints on a table
DBCC CHECKCONSTRAINTS('Production.Product');
GO
/* DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
*/

------------

------------
-- Single trigger disable and enable
------------
-- SQL disable trigger
ALTER TABLE Sales.SalesOrderHeader DISABLE TRIGGER uSalesOrderHeader
GO

-- SQL enable trigger
ALTER TABLE Sales.SalesOrderHeader ENABLE TRIGGER uSalesOrderHeader
GO

------------

------------
-- Create a check constraint for a table and enable it
------------
-- SQL create check constraint
-- Range constraint - column value must be between 0 and 100
USE AdventureWorks;
ALTER TABLE [Production].[ProductInventory] WITH CHECK
ADD CONSTRAINT [CK_ProductInventory_Bin]
CHECK (([Bin]>=(0) AND [Bin]<=(100)))
GO

-- SQL enable check constraint
ALTER TABLE [Production].[ProductInventory]
CHECK CONSTRAINT [CK_ProductInventory_Bin]
GO