none
Drop Constraints, Truncate and Re-Create Constraints

    Question

  • Hi,

    I am working on a SP that for a given database;

    1. Drops the referential integrity constraints.
    2. Truncates the tables.
    3. Re-creates the integrity constraints.

    I started testing the code and the code seems to drop the constraints well but it kind of bugs while Truncating the tables.

    I am using INFORMATION_SCHEMA to generate the temp table of table's constraint list, the dynamic code generation for Truncation seems to fail.

    I desperately need to fix this on priority.

    Here is the code:

    -----------------------------------------------------------------------------------
    CREATE PROCEDURE usp_TruncateTableData_ConstraintDropAdd
    AS
    BEGIN 
     SET NOCOUNT ON;

     DECLARE
     @FK_TableSchema NVARCHAR(200),
     @FK_TableName NVARCHAR(200),
     @FK_Name NVARCHAR(200),
     @FK_ColumnName NVARCHAR(200),
     @PK_TableSchema NVARCHAR(200),
     @PK_TableName NVARCHAR(200),
     @PK_ColumnName NVARCHAR(200),
     @DROP VARCHAR(MAX),
     @ADD VARCHAR(MAX),
     @TRUNC VARCHAR(MAX)

     DECLARE @table TABLE(
        RowId INT PRIMARY KEY IDENTITY(1, 1),
        ForeignKeyConstraintName NVARCHAR(200),
        ForeignKeyConstraintTableSchema NVARCHAR(200),
        ForeignKeyConstraintTableName NVARCHAR(200),
        ForeignKeyConstraintColumnName NVARCHAR(200),
        PrimaryKeyConstraintName NVARCHAR(200),
        PrimaryKeyConstraintTableSchema NVARCHAR(200),
        PrimaryKeyConstraintTableName NVARCHAR(200),
        PrimaryKeyConstraintColumnName NVARCHAR(200)   
     )

     INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
     SELECT
        U.CONSTRAINT_NAME,
        U.TABLE_SCHEMA,
        U.TABLE_NAME,
        U.COLUMN_NAME
     FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
        ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
     WHERE
        C.CONSTRAINT_TYPE = 'FOREIGN KEY'   

     UPDATE @table SET
        PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
     FROM
        @table T
        INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
        ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

     UPDATE @table SET
        PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
        PrimaryKeyConstraintTableName  = TABLE_NAME
     FROM @table T
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
        ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

     UPDATE @table SET
        PrimaryKeyConstraintColumnName = COLUMN_NAME
     FROM @table T
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
        ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

     --SELECT * FROM @table

     ---------------------------------------------------------------------------------------------------------
     --DROP CONSTRAINT:

     DECLARE FK_DROP CURSOR FOR
      SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName
      FROM @table
     OPEN FK_DROP
     FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name
     WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @DROP = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] DROP CONSTRAINT ' + @FK_Name

      EXECUTE(@DROP)
      --PRINT @DROP

      FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name
     END
     CLOSE FK_DROP
     DEALLOCATE FK_DROP

     ---------------------------------------------------------------------------------------------------------
     --TRUNCATE TABLES:

     DECLARE FK_TRUNC CURSOR FOR
      SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName
      FROM @table
     OPEN FK_TRUNC
     FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName
     WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @TRUNC = 'TRUNCATE TABLE [' + @FK_TableName + ']'

      EXECUTE(@TRUNC)
      --PRINT @TRUNC

      FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName
     END
     CLOSE FK_TRUNC
     DEALLOCATE FK_TRUNC

     ---------------------------------------------------------------------------------------------------------
     --ADD CONSTRAINT:

     DECLARE FK_ADD CURSOR FOR
      SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName, ForeignKeyConstraintColumnName,
       PrimaryKeyConstraintTableSchema, PrimaryKeyConstraintTableName, PrimaryKeyConstraintColumnName
      FROM @table
     OPEN FK_ADD
     FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName
     WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @ADD = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] ADD CONSTRAINT ' + @FK_Name +
      ' FOREIGN KEY(' + @FK_ColumnName + ') REFERENCES [' + @PK_TableSchema + '].[' + @PK_TableName + '](' + @PK_ColumnName + ')' 

      EXECUTE(@ADD)
      --PRINT @ADD

      FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName
     END
     CLOSE FK_ADD
     DEALLOCATE FK_ADD

     ---------------------------------------------------------------------------------------------------------
       
    END
    GO
    -----------------------------------------------------------------------------------


    Cheers!! Sumit Bhatnagar

    Thursday, August 29, 2013 12:01 PM

All replies