TSQL to determine all FK’s in a database

Here is some handy TSQL to determine all the FK relationships in a database. The basic ideas is we can use this meta data to automatically determine if a form should have a Combo Box rendered instead of a textbox.

    --  List all Foreign key Relationships in User Tables 
    SELECT  so.name as ForeignTableName, sc.name  as ColumnName, 
    s.name + '.' + PKT.name as PrimaryTableName
    from sysreferences r
    inner join  sysconstraints c on r.constid = c.constid
    inner join sys.tables pkt on PKT.object_id = r.rkeyid
    inner join sys.columns sc on sc.column_id = r.fkey1 and r.fkeyid = sc.object_id 
    inner join sys.schemas s on s.schema_id=pkt.schema_id
    inner join sys.tables so on r.fkeyid = so.object_id
    where so.is_ms_shipped=0 
    order by so.name, sc.name

Leave a Reply