How to identify Foreign Keys from sys tables in SQL Server?

Question

I used the following query to return Primary Keys of two tables. Is there a similar way to query for the Foreign Keys? I am not familiar with creating DBs but I dont see any Foreign Keys defined in the Object Explorer in SSMS. Thanks

select schema_name(tab.schema_id) as [schema_name], 
    pk.[name] as pk_name,
    ic.index_column_id as column_id,
    col.[name] as column_name, 
    tab.[name] as table_name
from sys.tables tab
    inner join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
    inner join sys.index_columns ic
        on ic.object_id = pk.object_id
        and ic.index_id = pk.index_id
    inner join sys.columns col
        on pk.object_id = col.object_id
        and col.column_id = ic.column_id

where tab.name = 'custtable' or tab.name = 'custtrans'

order by schema_name(tab.schema_id),
    pk.[name],
    ic.index_column_id

Here is the output, but I need this to return Foreign Keys for these two tables.

schema_name     pk_name     column_id       column_name    table_name
dbo         I_077ACCOUNTIDX         1        ACCOUNTNUM     CUSTTABLE
dbo         I_077ACCOUNTIDX         2        DATAAREAID     CUSTTABLE
dbo         I_077ACCOUNTIDX         3        PARTITION      CUSTTABLE
dbo         I_078RECID              1        RECID          CUSTTRANS

**[Take a peek](https://stackoverflow.com/q/483193/6426692)** — Ilyes, Sep 19 '19 at 15:28
looks like the DB architect did not define any FKs. I am assuming for performance reasons. This explains why i can query and return PKs, but not FKs. Found a very good article on this https://dataedo.com/blog/why-there-are-no-foreign-keys-in-your-database-referential-integrity-checks — , Sep 19 '19 at 16:43

score 0 · Answer 1 · answered Sep 19 '19 at 16:15

Does this work for you? (This handles multi-key references by displaying them as comma-separated lists of columns, just FYI):

SELECT fkeys.[name] AS FKName, 
        OBJECT_NAME(fkeys.parent_object_id) AS TableName,
        (SELECT STUFF((SELECT ',' + c.[name]
         FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
              INNER JOIN sys.columns as c ON t.object_id = c.object_id
              INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.parent_column_id 
                                                      AND fc.constraint_object_id = fk.object_id 
                                                      AND fc.parent_object_id = fk.parent_object_id 
        WHERE fk.[name] = fkeys.[name]
        FOR XML PATH ('')), 1, 1, '')) AS FKFolumns,
        OBJECT_NAME(fkeys.referenced_object_id) AS ReferencedTableName,
        (SELECT STUFF((SELECT ',' + c.[name]
         FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
              INNER JOIN sys.columns as c ON t.object_id = c.object_id
              INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.referenced_column_id 
                                                      AND fc.constraint_object_id = fk.object_id 
                                                      AND fc.referenced_object_id = fk.referenced_object_id 
        WHERE fk.[name] = fkeys.[name]
        FOR XML PATH ('')), 1, 1, '')) AS ReferencedFKFolumns
   FROM sys.foreign_keys fkeys
  ORDER BY FKName;

How to identify Foreign Keys from sys tables in SQL Server?

1 Answers1