SQL Server: Howto get foreign key reference from information_schema?

Question

In SQL Server, how can I get the referenced table + column name from a foreign key?

Note: Not the table/column where the key is in, but the key it refers to.

Example:

When the key [FA_MDT_ID] in table [T_ALV_Ref_FilterDisplay]. refers to [T_AP_Ref_Customer].[MDT_ID]

such as when creating a constraint like this:

ALTER TABLE [dbo].[T_ALV_Ref_FilterDisplay]  WITH CHECK ADD  CONSTRAINT [FK_T_ALV_Ref_FilterDisplay_T_AP_Ref_Customer] FOREIGN KEY([FA_MDT_ID]) REFERENCES [dbo].[T_AP_Ref_Customer] ([MDT_ID]) GO 

I need to get [T_AP_Ref_Customer].[MDT_ID] when given [T_ALV_Ref_FilterAnzeige].[FA_MDT_ID] as input

Answer 1

Never mind, this is the correct answer:
http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

SELECT       KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA      ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME      ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA      ,KCU1.TABLE_NAME AS FK_TABLE_NAME      ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME      ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION      ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA      ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME      ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA      ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME      ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME      ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1      ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG       AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA      AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2      ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG       AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA      AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME      AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION  

Note:
Information_schema doesn't contain indices (it does find unique-contraints).
So if you want to find foreign-keys based on unique-indices, you need to go over the microsoft proprietary tables:

SELECT        fksch.name AS FK_CONSTRAINT_SCHEMA      ,fk.name AS FK_CONSTRAINT_NAME       ,sch1.name AS FK_TABLE_SCHEMA      ,t1.name AS FK_TABLE_NAME      ,c1.name AS FK_COLUMN_NAME      -- The column_id is not the ordinal, it can be dropped and then there's a gap...      ,COLUMNPROPERTY(c1.object_id, c1.name, 'ordinal') AS FK_ORDINAL_POSITION       ,COALESCE(pksch.name,sch2.name) AS REFERENCED_CONSTRAINT_SCHEMA      ,COALESCE(pk.name, sysi.name) AS REFERENCED_CONSTRAINT_NAME       ,sch2.name AS REFERENCED_TABLE_SCHEMA      ,t2.name AS REFERENCED_TABLE_NAME      ,c2.name AS REFERENCED_COLUMN_NAME      ,COLUMNPROPERTY(c2.object_id, c2.name, 'ordinal') AS REFERENCED_ORDINAL_POSITION  FROM sys.foreign_keys AS fk   LEFT JOIN sys.schemas AS fksch      ON fksch.schema_id = fk.schema_id   -- not inner join: unique indices  LEFT JOIN sys.key_constraints AS pk     ON pk.parent_object_id = fk.referenced_object_id      AND pk.unique_index_id = fk.key_index_id   LEFT JOIN sys.schemas AS pksch      ON pksch.schema_id = pk.schema_id   LEFT JOIN sys.indexes AS sysi      ON sysi.object_id = fk.referenced_object_id      AND sysi.index_id = fk.key_index_id   INNER JOIN sys.foreign_key_columns AS fkc      ON fkc.constraint_object_id = fk.object_id   INNER JOIN sys.tables AS t1      ON t1.object_id = fkc.parent_object_id   INNER JOIN sys.schemas AS sch1      ON sch1.schema_id = t1.schema_id   INNER JOIN sys.columns AS c1      ON c1.column_id = fkc.parent_column_id      AND c1.object_id = fkc.parent_object_id   INNER JOIN sys.tables AS t2      ON t2.object_id = fkc.referenced_object_id   INNER JOIN sys.schemas AS sch2      ON sch2.schema_id = t2.schema_id   INNER JOIN sys.columns AS c2      ON c2.column_id = fkc.referenced_column_id      AND c2.object_id = fkc.referenced_object_id 

Proof-test for edge-cases:

CREATE TABLE __groups ( grp_id int, grp_name varchar(50), grp_name2 varchar(50) ) ALTER TABLE __groups ADD CONSTRAINT UQ___groups_grp_name2 UNIQUE (grp_name2) CREATE UNIQUE INDEX IX___groups_grp_name ON __groups(grp_name)  GO CREATE TABLE __group_mappings( map_id int, map_grp_name varchar(50), map_grp_name2 varchar(50), map_usr_name varchar(50) ) GO  ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups FOREIGN KEY(map_grp_name) REFERENCES __groups (grp_name) GO   ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups2 FOREIGN KEY(map_grp_name2) REFERENCES __groups (grp_name2) GO   SELECT @@VERSION -- Microsoft SQL Server 2016 (SP1-GDR) (KB4458842) SELECT version() -- PostgreSQL 9.6.6 on x86_64-pc-linux-gnu GO 

Answer 2

If you can live with using the SQL Server specific schema catalog views, this query will return what you're looking for:

SELECT       fk.name,     OBJECT_NAME(fk.parent_object_id) 'Parent table',     c1.name 'Parent column',     OBJECT_NAME(fk.referenced_object_id) 'Referenced table',     c2.name 'Referenced column' FROM      sys.foreign_keys fk INNER JOIN      sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN     sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id INNER JOIN     sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id 

Not sure how - if at all - you can get the same information from the INFORMATION_SCHEMA views....