t-sql – How to Find Foreign Keys

Hi all ,

today we’ll talk about Foreign Keys, and specifically how to get all foreign keys linked to  a table :

we need to use sys.foreign_keys and sys.foreign_key_columns 

 

DECLARE @ObjectName as varchar(100)
SET @objectName = '' -- TableName
       
SELECT foreignKey.name AS ForeignKey,
      OBJECT_NAME(foreignKey.parent_object_id) AS TableName,
      COL_NAME(foreignKeyColumns.parent_object_id,
foreignKeyColumns.parent_column_id) AS ColumnName,
      OBJECT_NAME (foreignKey.referenced_object_id) AS ReferenceTableName,
      COL_NAME(foreignKeyColumns.referenced_object_id,
foreignKeyColumns.referenced_column_id) AS ReferenceColumnName,
      delete_referential_action_desc as DeleteAction,
      update_referential_action_desc as UpdateAction
     
FROM sys.foreign_keys AS foreignKey
INNER JOIN sys.foreign_key_columns AS foreignKeyColumns
      ON foreignKey.OBJECT_ID = foreignKeyColumns.constraint_object_id
 
WHERE OBJECT_NAME (foreignKey.referenced_object_id) = @objectName
OR OBJECT_NAME(foreignKey.parent_object_id)= @objectName