CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
user459295
user459295

Reputation: 127

Enable constraint

I have a huge database with many constraints and I have updated some data disabling constraint and I just want to enable them back.

I have used below query

EXEC sp_msforeachtable 'ALTER TABLE MyTable NOCHECK CONSTRAINT all' 
exec sp_msforeachtable @command1='print ''MyTable''', @command2='ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT all'

But when I run the enable query it takes a long time due to huge volume of data. It take more than 1 hour.

I just want to enable it quickly in the proper way without having any issues.

Please guide me how to overcome this issue.

Upvotes: 1

Views: 1310

Answers (3)

Filip De Vos
Filip De Vos

Reputation: 11908

Using sp_msforeachtable runs the statements sequential. Most likely spreading things out over a few threads will speed up the process a lot. With Service Broker this can be done in SQL Code.

(Below is code written from memory, I have no SQL Server available to test this on. So there might be some bugs in it. I will update whenever I have a Server available.)

First create an activation procedure

  CREATE PROC p_enable_constraints
  AS
     DECLARE @handle UNIQUEIDENTIFIER
           , @message sysname
           , @sql nvarchar(max)

     WHILE 1=1
       BEGIN
         BEGIN TRAN
         WAITFOR ( RECEIVE TOP(1) @handle = conversation_handle,
                              @message = message_body
                   FROM ConstraintQueueReceive), TIMEOUT 1000;
        IF @@rowcount = 0
          begin
            rollback
            break;
          end

        set @sql = N'ALTER TABLE ' + quotename(@message) + N' WITH CHECK CONSTRAINT ALL'
        exec (@sql)
        if @@error <> 0
          begin
            rollback tran
            break
          end 
        COMMIT TRANSACTION
      END
    RETURN(0)
   GO

Set up the Queues and Services to send and receive messages

  CREATE QUEUE ConstraintQueueSend
  CREATE SERVICE ConstraintServiceSend

  CREATE QUEUE ConstraintQueueReceive 
    WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = p_enable_constraints
                                 , MAX_QUEUE_READERS = 8
                                 , EXECUTE AS SELF);

  CREATE SERVICE [ConstraintServiceReceive] ON QUEUE ConstraintQueueReceive

Now create a procedure to submit a message for each table

  CREATE PROC p_submit_enable_constraints_message @object sysname
  AS
    DECLARE @handle UNIQUEIDENTIFIER;
    DECLARE @message sysname;

    BEGIN TRANSACTION;
    BEGIN DIALOG @handle FROM SERVICE [ConstraintServiceSend] 
                           TO SERVICE [ConstraintServiceReceive]
    WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @handle (@object);

    COMMIT TRANSACTION;

    END CONVERSATION @handle
    GO

Finally we can call the procedure to submit this to the queue.

   EXEC sp_msforeachtable 'EXEC p_submit_enable_constraints_message N''?'''

Ideally the result of the alter table is captured and sent back.

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32707

One thing to check is if you have indexes on both sides of the constraint (a good idea during normal operations, btw). To make this explicit, let's say that you have two tables (tbl_a and tbl_b) that have a FK constraint between them (let's say it's col_c). You'd want an index where the leading column is col_c on both tbl_a and tbl_b so that it doesn't have to table scan the table that doesn't have such an index.

Also, checking each constraint individually (rather than doing a "check constraint all") will give you better granularity should you need to stop in the middle.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Doing it "proper way without having any issue" is going to take time. You could improve the speed by omitting the WITH CHECK option, but then you'll end up with untrusted constraints in your DB, so I would not recommend that option.

Upvotes: 3

Related Questions