SQL Server – Looping through a table list to disable, renable constraints or add a new column

The Need:

The basic need a lot of times is to loop through a list of something and do something else for the items in the list that qualify – like disabling/re-enabling constraints for a set of tables or adding/dropping a column etc. We will see three examples below that act on such a list of tables.

  • Disable constraints
  • Re-enable constraints
  • Add a new column

There are various reasons why one would disable all the constraints in certain tables or all the tables in a database – the primary among them being to load data without giving any consideration to the order in which the tables are loaded.

I already did a similar post on this before
Disable constraints/triggers in SQL Server to populate with seed data and re-enable

The list of tables itself whose constraints need to be disabled could be on a completely different server. If you are wondering why the table list is saved twice in the code below, the first time we get the list from the source server (possibly) and then the temp table #ControlTable is on the database/server on which constraints need to be disabled.

Whatever your need is, the below script will disable all constraints in a set of tables of a database. You can easily change it to suit your needs.

Disable constraints

---------------------------------
--Disable all constraints.
---------------------------------

declare @MetaDataSourceServer VARCHAR(125) = '<MY_SERVER>'
declare @MetaDataSourceDB VARCHAR(125) = '<MY_DATABASE>'
declare @TableID int;
--TableListTableTemp is a physical table which has the list of tables whose constraints have to be disabled
--The TableListTableTemp needs the following columns, TableID, SchemaName, TableName
--Alternatively, you may alter it to do it for every table there is in the DB..
--Save the list into a temporary table named TableListTableTemp to loop through
declare @SQL nvarchar(512) = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ''TableListTableTemp'')
                                    SELECT TOP 1000 TableID, SchemaName, TableName
                                    INTO TableListTableTemp
                                    FROM ' + QUOTENAME(@MetaDataSourceServer) + '.' + QUOTENAME(@MetaDataSourceDB) + '.' + 'dbo.TableListTable';

-- Create a permanent table with list of tables
BEGIN TRY
       PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
       PRINT @SQL;
       EXEC sp_ExecuteSQL @SQL;
       PRINT 'End: ' + CAST(getdate() AS VARCHAR);
END TRY
BEGIN CATCH
       --We need to keep going and reenable all constraints instead of giving up on first error!
       PRINT ERROR_MESSAGE();
END CATCH;

--Create a temp table to loop thru
SELECT TOP 1000 TableID, SchemaName, TableName
INTO #ControlTable
FROM TableListTableTemp;

WHILE EXISTS (SELECT 1 FROM #ControlTable)
BEGIN

    SELECT TOP 1
              @TableID = TableID,
              @SQL = 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' NOCHECK CONSTRAINT all'
    FROM #ControlTable
    ORDER BY TableID ASC ;

    BEGIN TRY
            -- Enable constraints on TableID
            PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
            PRINT @SQL;
            EXEC sp_ExecuteSQL @SQL;
            PRINT 'End: ' + CAST(getdate() AS VARCHAR);
    END TRY
    BEGIN CATCH
            --In my case, I need to keep going and reenable all constraints instead of giving up on first error!
			--Change this to suit your needs...either by rethrowing or acting on the error!
            PRINT 'Error occured:';
            PRINT ERROR_MESSAGE();
    END CATCH;

    DELETE #ControlTable
    WHERE TableID = @TableID;

END;

DROP TABLE #ControlTable;

DROP TABLE TableListTableTemp;

Re-enabling – With or without checks

---------------------------------
--Re-enable all constraints (still disabled).
---------------------------------

declare @MetaDataSourceServer VARCHAR(125) = '<MY_SERVER>'
declare @MetaDataSourceDB VARCHAR(125) = '<MY_DATABASE>'
declare @TableID int;
declare @SQL nvarchar(512) = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ''TableListTableTemp'')
                                                       select top 1000 TableID, SchemaName, TableName
                                                       into TableListTableTemp
                                                       from  ' + QUOTENAME(@MetaDataSourceServer) + '.' + QUOTENAME(@MetaDataSourceDB) + '.' + 'dbo.TableListTable';
DECLARE @TableName NVARCHAR(100) = '';
DECLARE @Message NVARCHAR(2048) = '';

-- Create a permanent table with list of MetaData.Tables
BEGIN TRY
       PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
       PRINT @SQL;
       EXEC sp_ExecuteSQL @SQL;
       PRINT 'End: ' + CAST(getdate() AS VARCHAR);
END TRY
BEGIN CATCH
       --We need to keep going and reenable all constraints instead of giving up on first error!
       PRINT ERROR_MESSAGE();
END CATCH;

--Create a temp table to loop thru (of only the tables that have disabled constraints)
SELECT TOP 1000 TableID, SchemaName, TableName
INTO #ControlTable
FROM TableListTableTemp
WHERE exists (
	--This gets the list of disabled constraints
	SELECT id, name, tbl = object_name(parent_obj)
	 FROM sysobjects
	 WHERE objectproperty(id, 'CnstIsDisabled') = 1
		AND parent_obj = object_id(QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName))
		);

WHILE EXISTS (SELECT 1 FROM #ControlTable)
BEGIN
	--Again, check constraints can be re-enabled with checking of the data (wise and the right thing to do but takes longer)
	--  or you can re-enable constraints without doing any checks and "trust" that everything was done right!
    SELECT TOP 1
              @TableID = TableID,
			  @TableName = QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName),
              @SQL = 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH CHECK CHECK CONSTRAINT all'
    FROM #ControlTable
    ORDER BY TableID ASC;

    BEGIN TRY
            -- Enable constraints on TableID
            PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
            PRINT @SQL;
            EXEC sp_ExecuteSQL @SQL;
            PRINT 'End: ' + CAST(getdate() AS VARCHAR);
    END TRY
    BEGIN CATCH
            --We need to keep going and reenable all constraints instead of giving up on first error!
            PRINT 'Error occured:';
            PRINT ERROR_MESSAGE();

			SET @Message = @Message + @TableName + '; ';
    END CATCH;

    DELETE #ControlTable
    WHERE TableID = @TableID;

END;

DROP TABLE #ControlTable;

DROP TABLE TableListTableTemp;

IF LEN(@Message) > 0
BEGIN
	SET @Message = 'Error occured when re-enabling constraints for tables: ' + @Message + CHAR(13) +
					'Use "DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS" to find the specifc errors';
    --THROW 50001, @Message, 1;
	RAISERROR (@Message,16,1);
END;

Adding new columns

The above code illustrates the basic code to loop through a set of tables to perform any action. While we are on the subject of looping, let us see an example of how to loop through a set of tables and add a new column named “ExecutionId” to them if the column does not already exist in the table

---------------------------------
--Add a new column named ExecutionId to tables
---------------------------------

declare @MetaDataSourceServer VARCHAR(125) = '<MY_SERVER>'
declare @MetaDataSourceDB VARCHAR(125) = '<MY_DATABASE>'
declare @TableID int;
declare @SQL nvarchar(512) = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ''TableListTableTemp'')
                                                       select top 1000 TableID, SchemaName, TableName
                                                       into TableListTableTemp
                                                       from  ' + QUOTENAME(@MetaDataSourceServer) + '.' + QUOTENAME(@MetaDataSourceDB) + '.' + 'dbo.TableListTable';

-- Create a permanent table with list of tables
BEGIN TRY
       PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
       PRINT @SQL;
       EXEC sp_ExecuteSQL @SQL;
       PRINT 'End: ' + CAST(getdate() AS VARCHAR);
END TRY
BEGIN CATCH
       --We need to keep going and reenable all constraints instead of giving up on first error!
       PRINT ERROR_MESSAGE();
END CATCH;

--Create a temp table to loop thru
SELECT TOP 1000 TableID, SchemaName, TableName
INTO #ControlTable
FROM TableListTableTemp cmt
WHERE NOT EXISTS (SELECT object_id
                           FROM sys.columns c
                           WHERE name='ExecutionId'
                                  AND OBJECT_ID(QUOTENAME(DB_NAME()) + '.' + QUOTENAME(cmt.SchemaName) + '.' + QUOTENAME(cmt.TableName)) = c.Object_Id
                                  );

WHILE EXISTS (SELECT 1 FROM #ControlTable)
BEGIN

    SELECT TOP 1
              @TableID = TableID,
              @SQL = 'alter table ' + QUOTENAME(SchemaName)+ '.' + QUOTENAME(TableName)  +
                           ' add [ExecutionId] BIGINT '
    FROM #ControlTable
    ORDER BY TableID asc;

    BEGIN TRY
            -- Enable constraints on TableID
            PRINT 'Start: ' + CAST(getdate() AS VARCHAR);
            PRINT @SQL;
            EXEC sp_ExecuteSQL @SQL;
            PRINT 'End: ' + CAST(getdate() AS VARCHAR);
    END TRY
    BEGIN CATCH
            --We need to keep going and reenable all constraints instead of giving up on first error!
            PRINT 'Error occured:';
            PRINT ERROR_MESSAGE();
    END CATCH;

    DELETE #ControlTable
    WHERE TableID = @TableID;

END;

DROP TABLE #ControlTable;

DROP TABLE TableListTableTemp;

Advertisements

One thought on “SQL Server – Looping through a table list to disable, renable constraints or add a new column

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