Home Ask Login Register

Developers Planet

Your answer is one click away!

Balah February 2016

List SQL Table Relationships with composite keys

What would be the SQL to list table relationships, with the composite keys/foreign fields correctly lined up together? Here's what I mean:

I use the SQL below to get the table relationships:

SELECT
    c.CONSTRAINT_NAME,
    cu.TABLE_SCHEMA AS ReferencingSchema,
    cu.TABLE_NAME AS ReferencingTable,
    cu.COLUMN_NAME AS ReferencingColumn,
    ku.TABLE_SCHEMA AS ReferencedSchema,
    ku.TABLE_NAME AS ReferencedTable,
    ku.COLUMN_NAME AS ReferencedColumn
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON
        cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
        ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME

Which works great for single-column primary keys, but when composite keys are involved things fall apart. e.g. for the FK below there are 2 columns that participate in the relationship, but 4 misaligned results are returned (because of cartesian products):

+-------------------------+------------------+-------------------+-------------------+------------------+
|CONSTRAINT_NAME          |ReferencingTable  | ReferencingColumn | ReferencedTable   | ReferencedColumn |
+-------------------------+------------------+-------------------+-------------------+------------------+
|FK_ACCOUNT_CHAINID_CHAIN | ACCOUNT          | CHAINID           | CHAIN             | CHAINID          |
|FK_ACCOUNT_CHAINID_CHAIN | ACCOUNT          | CHAINID           | CHAIN             | NTENTID          |
|FK_ACCOUNT_CHAINID_CHAIN | ACCOUNT          | NTENTID           | CHAIN             | CHAINID          |
|FK_ACCOUNT_CHAINID_CHAIN | ACCOUNT          | NTENTID           | CHAIN             | NTENTID          |
+-------------------------+------------------+-------------------+-------------------+------------------+

I would want the result to be:

+---------------------        

Answers


i-one February 2016

The following should give you what you need, however I used sys schema views, instead of INFORMATION_SCHEMA:

select
    quotename(fk.name) CONSTRAINT_NAME,
    quotename(s.name) + '.' + (t.name) ReferencingTable,
    quotename(c.name) ReferencingColumn,
    quotename(s2.name) + '.' + quotename(t2.name) ReferencedTable,
    quotename(c2.name) ReferencedColumn
from sys.foreign_keys fk
    join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
    join sys.tables t on t.object_id = fk.parent_object_id
    join sys.schemas s on s.schema_id = t.schema_id
    join sys.tables t2 on t2.object_id = fk.referenced_object_id
    join sys.schemas s2 on s2.schema_id = t2.schema_id
    join sys.columns c on c.column_id = fkc.parent_column_id and c.object_id = fk.parent_object_id
    join sys.columns c2 on c2.column_id = fkc.referenced_column_id and c2.object_id = fk.referenced_object_id
order by fk.name, fkc.constraint_column_id;

The above will return information for all FKs.

You can filter composite by adding where clause:

select ...
from ...
where
    (select count(1) from sys.foreign_key_columns where constraint_object_id = fk.object_id) > 1
order by fk.name, fkc.constraint_column_id;

Post Status

Asked in February 2016
Viewed 1,527 times
Voted 4
Answered 1 times

Search




Leave an answer


Quote of the day: live life