Drop And Recreate Constraints Script

I have a table names Employee which is referenced to almost 85 tables in database. I want to make few changing in the table structure and for that I have to drop and recreate the t

Solution 1:

-- =========================================================================
--                              Read the comments !!!
-- =========================================================================
DECLARE
    @source_table VARCHAR(50),
    @source_constarint VARCHAR(50),
    @source_column VARCHAR(50),
    @target_table VARCHAR(50),
    @target_column VARCHAR(50),
    @drop NVARCHAR(4000)

-- Remember the keys
SELECT 
    ccu.TABLE_NAME AS source_table
    ,ccu.CONSTRAINT_NAME AS source_constraint
    ,ccu.COLUMN_NAME AS source_column
    ,kcu.TABLE_NAME AS target_table
    ,kcu.COLUMN_NAME AS target_column
INTO #keys
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME 
WHERE ccu.TABLE_NAME ='TABLE NAME'

-- Delete keys
DECLARE drop_keys CURSOR FOR 
SELECT 
    source_table,
    source_constraint,
    source_column,
    target_table,
    target_column
FROM #keys
OPEN drop_keys
FETCH NEXT FROM drop_keys INTO @source_table, @source_constarint, @source_column, @target_table, @target_column
WHILE @@fetch_status <> -1
BEGIN
         SET @drop = 
         'ALTER TABLE ' + @source_table + ' DROP CONSTRAINT ' + @source_constarint 

         EXEC sp_executesql @drop

        SELECT @drop

        FETCH NEXT FROM drop_keys INTO @source_table, @source_constarint, @source_column, @target_table, @target_column
    END
    CLOSE drop_keys
    DEALLOCATE drop_keys


    -- =========================================================================
    --                      Your changes
    -- =========================================================================

    -- =========================================================================
    --                      Write a similar cursor to create keys
    --                      Make sure your cursor is creating keys
    -- =========================================================================

-- DROP table #keys -- Maybe just do not delete it until you not to recreate the keys
                    -- Maybe, Instead of inserting into a temporary table, insert it into a regular table