CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Balah
Balah

Reputation: 2540

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:

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

Now I could use a natural-like join on Referencing- and Referenced-columns to line up the fields - by adding AND cu.COLUMN_NAME = ku.COLUMN_NAME to the join, but that would only work if the names are the same and in many instances they are not (not of my doing).

I looked in INFORMATION_SCHEMA.KEY_COLUMN_USAGE and that has the Ordinal_Position field for ordering the primary keys, but I haven't seen a similar column in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and I'm not sure where else to look.

So if/where/how can I line up the referencing and referenced columns in a composite table relationship?

Applies to SQL Server 2008 R2 and above.

Upvotes: 1

Views: 80

Answers (1)

i-one
i-one

Reputation: 5120

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;

Upvotes: 1

Related Questions