Script to output information of foreign keys

Question

I would like to know the detail about my foreign keys.

I tried this

SELECT * FROM sys.foreign_keys;

But I need more than the information I can get now. Is there a way to know the columns they are refering to by using scripts like this ?

Answer 1

To get list of all tables and their column names, use the next code:-

select 
    t.name as TableWithForeignKey, 
    c.name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id in (select object_id 
                               from sys.tables )
order by 
    TableWithForeignKey

Reference:- How can I list all foreign keys referencing a given table in SQL Server?