vicker313 tech blog

November 5, 2010

Enable or Disable All Constraints in MSSQL

Filed under: MSSQL — Tags: , — vicker313 @ 8:11 pm

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.

Blog at WordPress.com.