How to disable all Foreign Key Constraint in SQL Server Database - SQL Server / TSQL Tutorial Part 77

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