Thursday, June 26, 2008

Disable/Enable All Constraints In A Database

Method 1

--Disable
DECLARE @SQL VARCHAR(MAX)
Select @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL;'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
EXEC(@SQL)

--Enable
DECLARE @SQL VARCHAR(MAX)
Select @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL;'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
EXEC(@SQL)

Method 2

--Disable
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Enable
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

0 comments: