SQL Server: Howto get foreign key reference from information_schema?

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)GOCREATE TABLE __group_mappings( map_id int, map_grp_name varchar(50), map_grp_name2 varchar(50), map_usr_name varchar(50) )GOALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups FOREIGN KEY(map_grp_name)REFERENCES __groups (grp_name)GOALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups2 FOREIGN KEY(map_grp_name2)REFERENCES __groups (grp_name2)GOSELECT @@VERSION -- Microsoft SQL Server 2016 (SP1-GDR) (KB4458842)SELECT version() -- PostgreSQL 9.6.6 on x86_64-pc-linux-gnuGO

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 fkINNER JOIN     sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_idINNER JOIN    sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_idINNER 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....

I wanted a a version that would let me find all the "Key" and "ID" columns having/missing a constraint. So i wanted all columns compared to the list of all PK OR FK OR Null, here is my query. Hope it helps someone else!

SELECT      c.table_schema    ,c.table_name    ,c.column_name    ,KeyConstraints.constraint_type    ,KeyConstraints.constraint_schema    ,KeyConstraints.constraint_name    ,KeyConstraints.referenced_table_schema    ,KeyConstraints.referenced_table_name    ,KeyConstraints.referenced_column_name    ,KeyConstraints.update_rule    ,KeyConstraints.delete_ruleFROM information_schema.columns AS c LEFT JOIN     (        SELECT              FK.table_schema AS TABLE_SCHEMA            ,FK.table_name            ,CU.column_name            ,FK.constraint_type            ,c.constraint_schema            ,C.constraint_name            ,PK.table_schema AS REFERENCED_TABLE_SCHEMA            ,PK.table_name AS REFERENCED_TABLE_NAME            ,CCU.column_name AS REFERENCED_COLUMN_NAME            ,C.update_rule            ,C.delete_rule        FROM information_schema.referential_constraints AS C         INNER JOIN information_schema.table_constraints AS FK             ON C.constraint_name = FK.constraint_name         INNER JOIN information_schema.table_constraints AS PK             ON C.unique_constraint_name = PK.constraint_name         INNER JOIN information_schema.key_column_usage AS CU             ON C.constraint_name = CU.constraint_name         INNER JOIN information_schema.constraint_column_usage AS CCU             ON PK.constraint_name = CCU.constraint_name         WHERE ( FK.constraint_type = 'FOREIGN KEY' )         UNION         SELECT              ccu.table_schema            ,ccu.table_name            ,ccu.column_name            ,tc.constraint_type            ,ccu.constraint_schema            ,ccu.constraint_name            ,NULL            ,NULL            ,NULL            ,NULL            ,NULL        FROM information_schema.constraint_column_usage ccu         INNER JOIN information_schema.table_constraints tc             ON ccu.table_schema = tc.table_schema             AND ccu.table_name = tc.table_name         WHERE tc.constraint_type = 'PRIMARY KEY'    ) AS KeyConstraints     ON c.table_schema = KeyConstraints.table_schema     AND c.table_name = KeyConstraints.table_name     AND c.column_name = KeyConstraints.column_name WHERE c.column_name LIKE '%ID' OR c.column_name LIKE '%Key' ORDER BY  c.table_schema          ,c.table_name          ,c.column_name ; 

formatting courtesy of: http://www.dpriver.com/pp/sqlformat.htm