Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constraints after you're done. This is most often done to improve performance during a bulk load operation, Some type of Database cleanup process.

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)