How to get the foreign and primary key details for a table in SQL Server 2008

Question

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys.

Is this query that lists the primary keys correct:

select syssc.name as schemaname , cast(c.name as  varchar(255)) as foreign_table , 
cast(p.name as varchar(255)) as primary_table 
from sysobjects f 
inner join sysobjects c on  f.parent_obj = c.id 
inner join sysreferences r on f.id =  r.constid 
inner join sysobjects p on r.rkeyid = p.id 
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid 
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid 
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid 
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid 
inner join sys.tables syst on rc.id=syst.object_id 
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id 
where  f.type =  'F' AND c.name in ('table name' )

This is the query that i use now to get the foreign keys

select syssc.name as schemaname ,cast(c.name as  varchar(255)) as foreign_table ,  
cast(p.name as varchar(255)) as primary_table 
from sysobjects f 
inner join sysobjects c on  f.parent_obj = c.id 
inner join sysreferences r on f.id =  r.constid 
inner join sysobjects p on r.rkeyid = p.id 
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid 
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid 
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid 
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid 
inner join sys.tables syst on rc.id=syst.object_id 
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id 
where  f.type =  'F' and p.name in ('table name')

Kindly check whether these queries are the right ones and the efficient ones.

Answer 1

You should become familiar with the new catalog views. The old sysobjects, syscolumns etc. have been deprecated and are only provided for backward compatibility reasons - they shouldn't be used for new development and old code should eventually transition to the new objects.

Here is a query that will get both sides of the columns involved in foreign keys:

SELECT 
        constraint_name   = OBJECT_NAME(fkc.constraint_object_id), 
        foreign_schema    = SCHEMA_NAME(child.[schema_id]), 
        foreign_table     = child.name, 
        foreign_column    = child_cols.name, 
        referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
        referenced_table  = referenced.name, 
        referenced_column = referenced_cols.name
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS child
    ON fkc.parent_object_id = child.[object_id]
    INNER JOIN sys.tables AS referenced
    ON fkc.referenced_object_id = referenced.[object_id]
    INNER JOIN sys.columns AS referenced_cols
    ON fkc.parent_column_id = referenced_cols.column_id
    AND referenced_cols.[object_id] = referenced.[object_id]
    INNER JOIN sys.columns AS child_cols
    ON fkc.referenced_column_id = child_cols.column_id
    AND child_cols.[object_id] = child.[object_id]
    WHERE referenced.name = 'table name'
    -- if you want the tables that a child table references, use this instead:
    -- WHERE child.name = 'table name'
    ORDER BY fkc.parent_column_id;

And this one is much simpler, just for the tables involved:

SELECT 
    constraint_name = name, 
    foreign_schema = OBJECT_SCHEMA_NAME(parent_object_id),
    foreign_table = OBJECT_NAME(parent_object_id), 
    referenced_schema = OBJECT_SCHEMA_NAME(referenced_object_id),
    referenced_table = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

EDIT adding some links to documentation (these mostly fall within the SQL Server 2008 R2 hierarchy but they're roughly equivalent for SQL Server 2008 as well):

• Catalog Views
• Querying the Catalog Views
• Querying the Catalog Views FAQ
• Metadata Functions