Foreign Key scripts

We need to have ready scripts when we need to clean all data with reseed identities. Please make changes in this as per your requirements.

Drop temp table

    if object_id('tempdb..#Fkey') is not null drop table #Fkey 

Create temp table with Foreign Key (FK) values

;WITH cte     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 
        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' 
        ) 
    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, 300000) AS parentColName 
        ,refSchema          
        ,refTableName 
        ,SUBSTRING(( 
                SELECT ',' + b.refColName + '' 
                FROM cte b 
                WHERE b.foreignKeyName = c.foreignKeyName 
                ORDER BY b.consColumn 
                FOR XML PATH('') 
                ), 2, 300000) AS refColName 
    
	into  #Fkey 
	FROM cte c 

Generate foreign key drop scripts from temp table

--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 #Fkey 

Generate foreign key scripts from temp table.

--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 #Fkey 
GO 
 

Other References:

How to find SQL server Object dependency

Find require index on tables

How to find the missing foreign key indexes