Foreign Key Mappings

1. WITH SQL SERVER 2000 TABLES. ORIGINAL ANSWER does not ALLOW multiple column relationships 1

select  
C.constid RELATIONSHIP_ID,  
P.name SOURCE_TABLE,  
Q.name SPURCE_COLUMN,  
F.name TARGET_TABLE,  
G.name TARGET_COLUMN, 
K.keyno CARDINALITY 
from sysconstraints C                                                -- general constraint  
join sysforeignkeys K on C.constid = K.constid and K.fkeyid = C.id   -- foreign key constraint 
join sysobjects P on P.id = K.rkeyid                                 -- source table 
JOIN syscolumns Q ON P.id = Q.id and Q.colid = K.rkey                -- source column 
join sysobjects F on F.id = C.id                                     -- target table 
JOIN syscolumns G ON G.id = F.id and G.colid =  K.fkey               -- target column 
where P.xtype = 'U' and F.type='U'

1. with >2005 views

DECLARE @Intermediate TABLE (PRIMARY_KEY_NAME sysname, FOREIGN_KEY_NAME sysname, PRIMARY_TABLE sysname, PRIMARY_COLUMN sysname, FOREIGN_TABLE sysname, FOREIGN_COLUMN sysname, COLINDEX int) 
insert into @Intermediate 
select  
c.name PRIMARY_KEY_NAME, 
fk.name FOREIGN_KEY_NAME, 
tp.name PRIMARY_TABLE, 
pc.name PRIMARY_COLUMN, 
tf.name FOREIGN_TABLE, 
fc.name FOREIGN_COLUMN, 
fkc.constraint_column_id COLINDEX 
from sys.foreign_keys fk  
join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id and fk.parent_object_id = fkc.parent_object_id and fk.referenced_object_id = fkc.referenced_object_id 
join sys.key_constraints kc on kc.parent_object_id = fk.referenced_object_id 
join sys.tables tp on tp.object_id = fk.referenced_object_id 
join sys.tables tf on tf.object_id = fk.parent_object_id 
join sys.columns pc on pc.object_id = fkc.referenced_object_id and pc.column_id=fkc.referenced_column_id 
join sys.columns fc on fc.object_id = fkc.parent_object_id and fc.column_id=fkc.parent_column_id 


SELECT I.PRIMARY_KEY_NAME, I.FOREIGN_KEY_NAME, i.PRIMARY_TABLE, I.FOREIGN_TABLE, 
stuff((select distinct ', '+I2.PRIMARY_COLUMN as [text()] from @Intermediate I2   
WHERE I2.PRIMARY_TABLE = I.PRIMARY_TABLE AND I2.FOREIGN_TABLE = I.FOREIGN_TABLE AND I2.FOREIGN_KEY_NAME = I.FOREIGN_KEY_NAME AND I2.PRIMARY_KEY_NAME = I.PRIMARY_KEY_NAME 
for xml path('')),1,1,'')  AS PRIMARY_COLUMN, 
stuff((select distinct ', '+I2.FOREIGN_COLUMN as [text()] from @Intermediate I2   
WHERE I2.PRIMARY_TABLE = I.PRIMARY_TABLE AND I2.FOREIGN_TABLE = I.FOREIGN_TABLE AND I2.FOREIGN_KEY_NAME = I.FOREIGN_KEY_NAME AND I2.PRIMARY_KEY_NAME = I.PRIMARY_KEY_NAME 
for xml path('')),1,1,'')  AS FOREIGN_COLUMN, 
COUNT(I.COLINDEX) AS CARDINALITY 
FROM @Intermediate I 
group by I.PRIMARY_KEY_NAME, I.FOREIGN_KEY_NAME, I.PRIMARY_TABLE, I.FOREIGN_TABLE

1. with information schema

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 'UQ_CONSTRAINT_NAME' 
   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME' 
   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME' 
   , KCU2.ORDINAL_POSITION AS 'UQ_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

1. SQL Server 2012

exec SP_FKEYS @pktable_name = 'mpk' 
exec SP_FKEYS @fktable_name = 'mfk

1. table hard dependency 2

SELECT 
 coalesce(OBJECT_SCHEMA_NAME(f.parent_object_id) + '.', '') + OBJECT_NAME(f.parent_object_id) PARENT, 
 coalesce(OBJECT_SCHEMA_NAME(f.referenced_object_id) + '.', '') + OBJECT_NAME(f.referenced_object_id) REF
FROM sys.foreign_keys f
WHERE 1=1 
--AND f.referenced_object_id = OBJECT_ID('dbo.Orders')
AND f.parent_object_id != referenced_object_id

1. sys.sql_expression_dependencies 3

 SELECT
    coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name
      object_name(Referencing_ID)+ --definite entity name
      coalesce('.'+col_name(referencing_ID,referencing_minor_id),'')
         AS [referencing],
    coalesce(Referenced_server_name+'.','')+ --possible server name if cross-server
         coalesce(referenced_database_name+'.','')+ --possible database name if cross-database
         coalesce(referenced_schema_name+'.','')+ --likely schema name
         coalesce(referenced_entity_name,'') + --very likely entity name
         coalesce('.'+col_name(referenced_ID,referenced_minor_id),'')AS [referenced]
  FROM sys.sql_expression_dependencies
  --WHERE referencing_id =object_id('Categories')
  ORDER BY [referenced]

<

«