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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: