How to find all the relations between all tables with INFORMATION_SCHEMA in SQL Server?

Question

Is there a way to get all relations between all db tables by using INFORMATION_SCHEMA?

I tried to use INFORMATION_SCHEMA.KEY_COLUMN_USAGE to get it, because it works with MySQL, but it doesn't have REFERENCED_COLUMN_NAME and REFERENCED_TABLE_NAME columns in SQL Server.

If you want more bespoke information, specific to the RDBMS, you are better off using the bespoke objects for the RDBMS. In SQL Server, that's the `sys` schema. — Thom A, May 25 '21 at 17:51
Does this answer your question? [How can I list all foreign keys referencing a given table in SQL Server?](https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) — Charlieface, May 25 '21 at 21:46

score 1 · Accepted Answer · answered May 26 '21 at 06:29

You need a bit more of those views to get what you need:

List of FKs with both ends specified is in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Mapping between referenced key and its table name is in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Something like this should work:

select cu.CONSTRAINT_NAME as [ForeignKeyName],
    cu.TABLE_SCHEMA as [ReferencingTableSchema],
    cu.TABLE_NAME as [ReferencingTableName],
    cu.COLUMN_NAME as [ReferencingColumnName],
    tc.TABLE_SCHEMA as [ReferencedTableSchema],
    tc.TABLE_NAME as [ReferencedTableName],
    tc.CONSTRAINT_TYPE,
    rc.UNIQUE_CONSTRAINT_NAME as [ReferencedKeyName]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
        and tc.TABLE_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
order by cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.CONSTRAINT_NAME, cu.COLUMN_NAME;

The query is probably not 100% fool-proof, you need to check with multi-column FKs, and possibly relationships between tables in different schemas. Still, a good starting point.

Oh, and if you need a column name from the parent table, you will need the KEY_COLUMN_USAGE again, this time for the referenced table. Might be tricky with multi-column FKs, look at the ORDINAL_POSITION column, it should give you the correlation which child column references which parent one.

How to find all the relations between all tables with INFORMATION_SCHEMA in SQL Server?

1 Answers1