FK_Add_Drop_Constraints_AllDBs

/*Generate #temp table with constraints information
Author: AG*/ 

--drop table #FkeyDesc 
USE <DBName>
GO

SET NOCOUNT ON
GO
    ;WITH cte ( 
        consColumn 
        ,foreignKeyName 
        ,parentSchema 
        ,parentTableName 
        ,parentColName 
        ,refSchema 
        ,refTableName 
        ,refColName  
        ) 
    AS ( 
        SELECT fkc.constraint_column_id AS consColumn 
            ,fk.name AS foreignKeyName 
            ,parentSchema .name AS parentSchema 
            ,parentTable.name AS parentTableName 
            ,parent_col.name AS parentColname 
            ,refSchema.name as refSchema 
            ,refTable.name AS refTablename 
            ,ref_col.name AS refColName 
        --select *   
        FROM sys.foreign_keys fk 
        INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id 
        INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id 
        INNER JOIN sys.schemas  parentSchema ON parentSchema .schema_id=parentTable .schema_id  
        INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id 
            AND parent_col.object_id = parentTable.object_id 
        INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id 
        INNER JOIN sys.schemas refSchema ON refSchema .schema_id =refTable .schema_id  
        INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id 
            AND ref_col.object_id = refTable.object_id 
            AND parentTable.type='U'  
            AND refTable.type='U' 
            --AND parentTable.schema_id =schema_id('CUBE') 
            --AND refTable.schema_id =schema_id('CUBE')  
        --AND parentTable.name='luExitReason'  
           -- AND refTable.name='luExitReason'  --like 'DIM!_%' ESCAPE'!' OR refTable.name like 'FCT!_%' ESCAPE'!') 
        ) 
    SELECT DISTINCT foreignKeyName 
        ,parentSchema  
        ,parentTableName 
        ,SUBSTRING(( 
                SELECT ',' + a.parentColName + '' 
                FROM cte a 
                WHERE a.foreignKeyName = c.foreignKeyName 
                ORDER BY a.consColumn 
                FOR XML PATH('') 
                ), 2, 200000) AS parentColName 
        ,refSchema          
        ,refTableName 
        ,SUBSTRING(( 
                SELECT ',' + b.refColName + '' 
                FROM cte b 
                WHERE b.foreignKeyName = c.foreignKeyName 
                ORDER BY b.consColumn 
                FOR XML PATH('') 
                ), 2, 200000) AS refColName 
    INTO #FkeyDesc 
    FROM cte c 
 
--Dropping foreign key constraints. 
    SELECT DISTINCT 'IF  EXISTS (SELECT * FROM sys.foreign_keys  
        WHERE object_id = OBJECT_ID(N''['+parentSchema+'].[' + foreignKeyName + ']'')  
        AND parent_object_id = OBJECT_ID(N''['+parentSchema+'].[' + parentTableName + ']'')) 
        ALTER TABLE ['+parentSchema+'].[' + parentTableName + '] DROP CONSTRAINT [' + foreignKeyName + ']' AS foreignKey_drop_script 
        FROM #FkeyDesc 
         
--Creating foreign key constraints. 
SELECT DISTINCT 'ALTER TABLE ['+parentSchema +'].[' + parentTableName + ']  WITH CHECK  
        ADD  CONSTRAINT [' + foreignKeyName + '] FOREIGN KEY(' + parentColName + ') 
        REFERENCES ['+refSchema+'].[' + refTableName + '](' + refColName + ')' AS Add_constraints_script 
        FROM #FkeyDesc 
GO 
 
 
/* Below is the example table   
drop table Parent_Table 
drop table child_table 
drop table Parent_Table1 
drop table child_table1 
 
 
Create table Parent_Table(id int , name varchar(40),sal float constraint prk primary key (id,name) ) 
create table child_table(id int ,name varchar(40),dept int ,constraint frk foreign key (id,name) references Parent_Table(id,name)) 
    ; 
 
CREATE TABLE Parent_Table1 ( 
    id INT 
    ,NAME VARCHAR(40) 
    ,sal FLOAT CONSTRAINT prk1 PRIMARY KEY ( 
        id 
        ,NAME 
        ,sal 
        ) 
    ) 
CREATE TABLE child_table1 ( 
    id INT 
    ,NAME VARCHAR(40) 
    ,sal FLOAT 
    ,dept INT 
    ,CONSTRAINT frk1 FOREIGN KEY ( 
        id 
        ,NAME 
        ,sal 
        ) REFERENCES Parent_Table1(id, NAME, sal) 
    ); 
 
*/