Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What version of SQL Server please? –  Aaron Bertrand Sep 12 '11 at 13:42
    
SQL Server 2008 Express Edition. –  saravanan Sep 12 '11 at 14:28
    
And are you trying to get the columns in table name that are referenced by foreign keys in other tables, or the columns in table name that reference other tables? –  Aaron Bertrand Sep 12 '11 at 14:33
    
@Aaron: It was late night here,so only i was not able to reply to you. I needed both the tables and the columns but have to run separate queries now. I will use your queries in your reply. Thank you. –  saravanan Sep 13 '11 at 3:18

1 Answer 1

up vote 8 down vote accepted

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):

share|improve this answer
    
Thanks for your help. These queries really help me. I did not have the in depth knowledge on the database objects, hence had to use a suggested query, however i felt that it was a bit unclear for me. Hence I posted here. I will learn about the database objects, hope msdn has some good documentation, else you can suggest me one. Your script is clear and easy to understand than the one i have.. Thank you. –  saravanan Sep 13 '11 at 3:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.