Scenario:
You are working as SQL Server developer, You need to provide the scripts to disable all the Foreign Key Constraint in SQL Server database. This might be scenario where you need to load one time data and you are ok if data violate referential integrity.Solution:
Let's get the list of Foreign Key Constraints from a SQL Server Database before we generate the disable script.
--Get List of Foreign Key Constraints if Enabled or Disabled USE YourDatabaseName GO Select Schema_name(Schema_id) as SchemaName, object_name(Parent_object_id) as TableName, name as ForeignKeyConstraintName, Case When Is_disabled=1 Then 'No' ELSE 'Yes' End as IsEnabled from sys.foreign_keys
How to Check if Foreign Key Constraint is Enabled or Disabled in SQL Server |
Now let's generate script to Disable Foreign Key Constraint in SQL Server Database
USE YourdatabaseName go -- Drop Foreign Key Constraints Script SELECT distinct 'ALTER TABLE ' + '['+ Schema_name(FK.schema_id) + '].['+ OBJECT_NAME(FK.parent_object_id) + ']'+ ' NOCHECK CONSTRAINT ' + '[' + FK.name + ']' AS DisableConstraintQuery FROM sys.foreign_keys AS FK where is_disabled=0
How to generate Script to Disable All Foreign Key Constraints in SQL Server Database |
Video Demo : How to disable all Foreign Key Constraint in SQL Server Database
No comments:
Post a Comment