How to: Enable/Disable single constraint
According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement. for more details go to ALTER TABLE (Transact-SQL)
for an example:
/***** disable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/
ALTER TABLE dbo.TABLE2 NOCHECK CONSTRAINT FK_TABLE2_TABLE1
/***** enable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/
ALTER TABLE dbo.TABLE2 CHECK CONSTRAINT FK_TABLE2_TABLE1
How to: Enable/Disable all constraint
The following example shows how to disable all constraints on a table.
here i'm creating T-SQL statement for all tables using INFORMATION_SCHEMA View and execute them
--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)
go
--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)
No comments:
Post a Comment