Stored Procedure to Generate Drop Create Reference Key

sp_GenDropCreateRefsFkScript


USE [Work] --DB Name
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreateRefsFkScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreateRefsFkScript]
GO


USE [Work]  --DB Name
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreateRefsFkScript]
    @SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
    
AS   


DECLARE  
   @FK_NAME SYSNAME,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME SYSNAME,  
   @FKTABLE_OWNER SYSNAME,  
   @PKTABLE_NAME SYSNAME,  
   @PKTABLE_OWNER SYSNAME,  
   @FKCOLUMN_NAME SYSNAME,  
   @PKCOLUMN_NAME SYSNAME,  
   @CONSTRAINT_COLID INT, 
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX)
    
DECLARE CSR_FKS CURSOR FOR   
   SELECT  fk.name,  
           fk.OBJECT_ID,   
           fk.is_disabled,   
           fk.is_not_for_replication,   
           fk.delete_referential_action,   
           fk.update_referential_action,   
           OBJECT_NAME(fk.parent_object_id) AS fk_table_name,   
           schema_name(fk.schema_id) AS fk_table_schema,   
           tbs.name AS pk_table_name,   
           schema_name(tbs.schema_id) pk_table_schema  
   FROM    sys.foreign_keys fk LEFT OUTER JOIN   
           sys.tables tbs ON tbs.OBJECT_ID = fk.referenced_object_id  
   WHERE   tbs.name = @TABLE_NAME 
           AND schema_name(tbs.schema_id) = @SCHEMA_NAME


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_FKS  
       
WHILE 1 = 1  
BEGIN
   FETCH NEXT FROM CSR_FKS   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER
   IF @@FETCH_STATUS <> 0
  BREAK 
   -- preparing drop statement 
   BEGIN 
  SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @DROPSQL = @DROPSQL + @DROPCND
       SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)
 END 
  
   -- preparing recreate statement 
 BEGIN 
  
       DECLARE 
@FKCOLUMNS VARCHAR(1000), 
@PKCOLUMNS VARCHAR(1000), 
@COUNTER INT

       -- get Foreign Key columns  
       DECLARE CSR_FKCOLS CURSOR FOR 
       SELECT  COL_NAME(fk.parent_object_id, fkc.parent_column_id) AS fk_col_name,   
               COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) AS pk_col_name  
       FROM    sys.foreign_keys fk LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = fk.referenced_object_id INNER JOIN   
               sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_ID   
       WHERE   tbs.name = @TABLE_NAME  
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND fkc.constraint_object_id = @FK_OBJECTID  
       ORDER BY fkc.constraint_column_id  
       OPEN CSR_FKCOLS  
       
       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  
         
       WHILE 1 = 1   
       BEGIN
           FETCH NEXT FROM  CSR_FKCOLS INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
           IF @@FETCH_STATUS <> 0
               BREAK
               
           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  
           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  
           SET @COUNTER = @COUNTER + 1  
           
       END  
       CLOSE CSR_FKCOLS   
       DEALLOCATE CSR_FKCOLS   
  
  SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @CREATECND + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
           CASE @FK_DISABLED   
               WHEN 0 THEN ' CHECK '  
               WHEN 1 THEN ' NOCHECK '  
           END +  ' ADD CONSTRAINT [' + @FK_NAME   
           + '] FOREIGN KEY (' + @FKCOLUMNS   
           + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
           + @PKCOLUMNS + ') ON UPDATE ' +   
           CASE @UPDATE_RULE   
               WHEN 0 THEN ' NO ACTION '  
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + ' ON DELETE ' +   
           CASE @DELETE_RULE  
               WHEN 0 THEN ' NO ACTION '   
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + '' +  
           CASE @FK_NOT_FOR_REPLICATION  
               WHEN 0 THEN ''  
               WHEN 1 THEN ' NOT FOR REPLICATION '  
           END  
           + ';' + CHAR(10) + 'END;' + CHAR(10)


   END
END  
CLOSE CSR_FKS   
DEALLOCATE CSR_FKS
GO