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

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....

Answer 2

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

SELECT 
     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
   , KCU2.CONSTRAINT_NAME AS 'REFERENCED_CONSTRAINT_NAME'
   , 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 RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 
   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 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