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

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.

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

StackExchange Database Administrators Q#5657, answer score: 10