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.

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
    SELECT tc.* 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
        AND tc.TABLE_NAME = 'MyTable'  
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

but for a PK returned from such a query how do I establish the ascociated FK (assuming there is one)?

Edit. I know you can also get the referenced tables via

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) = 'MyTable';
GO

but I am struggling now to get the explicit column references.

Thanks for your time.

share|improve this question
    
Do you want a query that returns the PK information along with all of the other FK data, or do you want a query that returns all of the FK data given a primary key constraint name that you supply to the query? –  Aaron Bertrand Jan 10 '13 at 19:24
    
I am creating a script genrator for QlikView. To generate the script I need the constraints and the ascociated links. –  Killercam Jan 10 '13 at 21:07
    
Well that doesn't really answer my question but I'm glad you got your answer in any case. :-) –  Aaron Bertrand Jan 10 '13 at 21:13
    
I need all of the constraint information for any given column (if any). I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs. Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both. Sorry if this also does not answer you question. –  Killercam Jan 11 '13 at 9:04
    
The simple answer is that I want both and the accepted answer gives me means to do this... –  Killercam Jan 11 '13 at 9:04
add comment

2 Answers

up vote 3 down vote accepted

Here's a simple query to match up foreign keys to their referenced tables/columns:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).

(Edited to add some more output columns.)

share|improve this answer
    
That is absolute quality. I have been hacking around with a three teir JOIN simalar to the 1st, 2nd and 4th above. Great to see an answer like this - so difinitive! Now, two additonal things if I may be so bold. How do you find out about the poentail links between such system table and thus easyly come up with the above? or is it experience? Exending this to include the actual PK name is also not obvious to me - could you extend the answer to include this please? –  Killercam Jan 10 '13 at 19:12
    
Ps. I am going to bounty this so you can have some rep! –  Killercam Jan 10 '13 at 19:13
1  
@Killercam It's a little of both; lots of experience with the tables/views, and the large SQL Server 2008 System Views poster hanging on the wall behind me. :) I'll see if I can work that back to the PK/unique index name... –  db2 Jan 10 '13 at 19:15
    
Thanks very much for your time here. It is most appreciated. Where can I get one of those posters!? It looks like you might be able to use INFORMATION_SCHEMA.KEY_COLUMN_USAGE? This is making me feel very weak! –  Killercam Jan 10 '13 at 19:18
1  
I don't know of any place that sells the posters regularly (I got mine when renewing SQL Server Magazine, I think), but you can download the PDF from MS: link. You could probably get Kinkos to print it in some large format. –  db2 Jan 10 '13 at 19:24
show 2 more comments

This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.

SELECT 
    FK = OBJECT_NAME(pt.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name), 
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;

If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:

DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';

SELECT
    FK = OBJECT_NAME(fkc.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name), 
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS 
(
  SELECT 1 FROM sys.indexes AS i
  INNER JOIN sys.foreign_keys AS fk
  ON i.[object_id] = fk.referenced_object_id
  AND i.index_id = fk.key_index_id
  AND fk.[object_id] = fkc.constraint_object_id
  AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;

If you just want to include the PK name along with the other information:

SELECT 
    FK = OBJECT_NAME(fkc.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name),
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name),
    PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
  FROM sys.indexes AS i
  INNER JOIN sys.foreign_keys AS fk
  ON i.[object_id] = fk.referenced_object_id
  AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;

There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.

share|improve this answer
    
Thanks for your time Aaron. Much appreciated. –  Killercam Jan 10 '13 at 19:45
add comment

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.