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 associated FK (assuming there is one)?
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.
I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. 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.
Best Answer
Here's a simple query to match up foreign keys to their referenced tables/columns:
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.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in
FK_columns
andPK_columns
, using the traditionalFOR XML
/STUFF
abuse. TheFK_indexes
column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it'sNULL
, then you've got an unindexed foreign key. You can tweak theORDER BY
, or add aWHERE
clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.