TechBrothersIT is a blog and YouTube channel sharing real-world tutorials, interview questions, and examples on SQL Server (T-SQL, DBA), SSIS, SSRS, Azure Data Factory, GCP Cloud SQL, PySpark, ChatGPT, Microsoft Dynamics AX, Lifecycle Services, Windows Server, TFS, and KQL. Ideal for data engineers, DBAs, and developers seeking hands-on, step-by-step learning across Microsoft and cloud platforms.
How to Generate Script To Enable All Foreign Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 78
Scenario:
You are working as SQL Server developer, You need to prepare scripts to enable all the Foreign Key Constraint in a database which are disabled. This could happen, maybe somebody has disabled Foreign Key Constraints for a data load where they don't care about violation of referential integrity and forgot to enable them later.
Solution:
First of all let's get the list of Foreign Key Constraints with status by using below query
--Get List of Foreign Key Constraints if Enabled or DisabledUSE YourDatabaseName
GOSelect
Schema_name(Schema_id) as SchemaName,
object_name(Parent_object_id) as TableName,
name as ForeignKeyConstraintName,
CaseWhen Is_disabled=1 Then'No'ELSE'Yes'Endas IsEnabled
from sys.foreign_keys
How to check if Foreign Key Constraint is Disabled or Enabled in SQL Server Database
Now let's generate scripts to enable the Foreign Key Constraints which are disabled in SQL Server Database by using below query.
USE YourdatabaseName
go-- Enable Foreign Key Constraints Script SELECTdistinct'ALTER TABLE '
+ '['+ Schema_name(FK.schema_id)
+ '].['+ OBJECT_NAME(FK.parent_object_id)
+ ']'+ ' CHECK CONSTRAINT '
+ '[' + FK.name + ']'AS EnableConstraintQuery
FROM sys.foreign_keys AS FK
where is_disabled=1
How to generate script to enable all Foreign Key Constraints in SQL Server Database
Copy the results and run in SSMS to enable disabled Foreign Key Constraints in SQL Server Database.
Video Demo : How to generate script to enable all Foreign Key Constraint in SQL Server
No comments:
Post a Comment