How to generate scripts to Re-create Foreign Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 73

Scenario:

You need to truncate all the tables in SQL Server database, when when you run truncate statement, you get error below error.
Msg 4712, Level 16, State 1, Line 43
Cannot truncate table 'SchemaName.TableName' because it is being referenced by a FOREIGN KEY constraint.

The easy way would be drop the Foreign Key Constraints, truncate the tables and recreate the Foreign Key Constraint again.

I wrote a post that you can use to generate Drop Foreign Key Constraints in a database. Click here. 
But before we drop them, we need to generate the create Foreign key Constraints scripts so we can run after truncating the table.

You can use below script to generate truncate table statement for all the user tables from a database.

Select 'Truncate table '+'['
+Schema_name(Schema_id)
+'].['+name+']' as TruncateTablesScript
from sys.tables
where is_ms_shipped=0


The below script can be used to re-generate Foreign Key Constraint in a database.

;With CTE_FK AS (
SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName,
  FK.name AS ForeignKeyConstraintName,c.name as RefColumnName,
  cf.name as ParentColumnList
       FROM sys.foreign_keys AS FK
       INNER JOIN sys.foreign_key_columns AS FKC
               ON FK.OBJECT_ID = FKC.constraint_object_id
               INNER JOIN sys.columns c
          on  c.OBJECT_ID = FKC.referenced_object_id
                 AND c.column_id = FKC.referenced_column_id
                 INNER JOIN sys.columns cf
          on  cf.OBJECT_ID = FKC.parent_object_id
                 AND cf.column_id = FKC.parent_column_id
                 where fk.is_ms_shipped=0
                 )
                 Select 
                 'Alter table ['+TableSchemaName+'].['+ParentTableName+']' 
                 +' Add Constraint '+ForeignKeyConstraintName+ 
                 ' Foreign Key('+stuff((
                 Select ','+ParentColumnList
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '')+') References '+
                 '['+TableSchemaName+'].['+ReferenceTableName+']('+stuff((
                 Select ','+RefColumnName
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '')+')'
                 AS CreateForeignKeyConstraintScript,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName
                 from CTE_FK o
                 group by 
                 tableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName



How to generate script to re-create Foreign Key Constraint in SQL Server Database

Take the results from CreateForeignKeyConstraintScript Column. I suggest you to run the scripts in DEV or QA first to make sure all working fine before you run in Production.



Video Demo : How to create generate scripts to recreate foreign Key Constraints in SQL Server

No comments:

Post a Comment