There is no method provided by MSSQL to enable or disable all constraints of table in a go. However we can create a simple procedure to archive this goal. Use script below to create the procedure:
CREATE PROCEDURE sp_ConstraintState
@TblName VARCHAR(128),
@State BIT = 1
AS
DECLARE @SQLState VARCHAR(500)
IF @State = 0
BEGIN
SET @SQLState = 'ALTER TABLE '+ @TblName + ' NOCHECK CONSTRAINT ALL'
END
ELSE
BEGIN
SET @SQLState = 'ALTER TABLE ' + @TblName + ' CHECK CONSTRAINT ALL'
END
EXEC (@SQLState)
go
To execute the procedure, run script below:
sp_MsForEachTable 'sp_ConstraintState ''?'', 0'
sp_MsForEachTable is system defined procedure in MSSQL, to run the procedure created above in each table. o at the back is to disable, 1 is for enable.
