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 |
Video Demo : How to create generate scripts to recreate foreign Key Constraints in SQL Server
No comments:
Post a Comment