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