Home
Browse Code Add-Ins Blog Language Feeds Service Stats FAQs Feedback About
Manage Your Code
Snippet: Disable and renable all database constraints (SQL)
Title: Disable and renable all database constraints Language: SQL
Description: Generates statements to turn off and turn on all constraints for a sql server table Views: 359
Author: John Yan Date Added: 4/26/2008
Copy Code   
/*To disable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable constraints on tables starting with a particular pattern*/
GO

/*To enable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable constraints on tables starting with a particular pattern*/
GO