:::: MENU ::::

Analyze all foreign keys in your SQL Server database

  • Dec 05 / 2011
  • 0
Data Modeling and Database Design, dbDigger, Monitoring and Analysis, T-SQL Scripts

Analyze all foreign keys in your SQL Server database

Creating foreign keys ensure data integrity across different tables in a database. However only creating these is not enough. Keeping in view the requirement we have to configure proper impact on foreign key value when its primary key is affected by update or delete operation.
SQL Server provides four rules for implementing the effect of primary key on foreign key value in case of update or delete operation. These are

  • No Action
  • Cascade
  • Set NULL
  • Set Default

In my related article on MSSQLTips, I have discussed the configuration and effect of these four rules. The emerged problem is to analyze the existing foreign keys for their configured rules for update and delete operations. It is required to generate a list of all foreign keys along with their associated columns and rules for update and delete operations. Below is the script that would provide a brief information to start with

SELECT fkey.name AS FKeyName,  
 object_name(fkey.parent_object_id) AS FKeyTable,  
 cols.name AS FKeyColumn,  
 object_name(fkey.referenced_object_id) AS PKeyTable,  
 colsa.name AS PKeyColumn,  
 CASE is_disabled WHEN 1 THEN 'YES'  
 WHEN 0 THEN 'No' END AS IsDisabled,  
 delete_referential_action_desc AS DeleteRule,  
 update_referential_action_desc AS UpdateRule  
 FROM sys.foreign_key_columns FKeyC  
 INNER JOIN sys.foreign_keys fkey  
 ON fkeyc.parent_object_id = fkey.parent_object_id  
 AND fkeyc.referenced_object_id = fkey.referenced_object_id  
 INNER JOIN sys.all_columns cols  
 ON fkeyc.parent_object_id = cols.object_id  
 AND parent_column_id = cols.column_id  
 INNER JOIN sys.all_columns colsa  
 ON fkeyc.referenced_object_id = colsa.object_id  
 AND parent_column_id = colsa.column_id  
 ORDER BY is_disabled DESC, delete_referential_action_desc, update_referential_action_desc  

You may also add create_date and modify_date in the select list for creation and modification date of respective foreign key. Once the list is available, analyze the requirement and configured rule along with disabled foreign keys.

Analyze all foreign keys in your SQL Server database

Consult us to explore the Databases. Contact us