What table a column is keyed to

Question

I have a query that gives me my column names, data type, max length, nullable...

I also need to know the name of the table the column refers to if it has a foreign key.

Here is my query

SELECT 
  c.name 'Column Name',
  t.Name 'Data type',
  c.max_length 'Max Length',
  c.is_nullable,
  ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
  sys.columns c
INNER JOIN 
  sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
  sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
  sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
  c.object_id = OBJECT_ID('Table_Name')

possible duplicate of [How can I find out what table is joined with a certain table?](http://stackoverflow.com/questions/11672211/how-can-i-find-out-what-table-is-joined-with-a-certain-table) — Aaron Bertrand, Jul 24 '13 at 21:43
Actually [this one is probably more useful](http://stackoverflow.com/questions/11161385/get-relations-of-a-table/). The point being, there are many questions here that deal with deriving metadata information about foreign keys, just look for `sys.foreign_keys` and `sys.foreign_key_columns` and you should find plenty of examples. — Aaron Bertrand, Jul 24 '13 at 21:45
Do you need to deal with multi-column FKs? What about (and I have seen such in the wild) a column that participates in multiple FKs? — Damien_The_Unbeliever, Jul 25 '13 at 06:31
All the foreign key columns only have one table in the foreign key. — runfastman, Jul 25 '13 at 13:29
All the examples give it backwards. I don't want to know who uses a given table, I want to know what tables this column refers to if any. — runfastman, Jul 25 '13 at 13:36

score 2 · Answer 1 · answered Jul 25 '13 at 14:37

Thanks to Devart, you got me on the right path.

Here is the answer for future reference

SELECT
      [Column Name] = c.name
    , [Data type] = t.name
    , [Max Length] = c.max_length
    , c.is_nullable
    , [Primary Key] = ISNULL(i.is_primary_key, 0)
    , OBJECT_NAME(fkc.referenced_object_id)
FROM sys.columns c WITH(NOWAIT)
JOIN sys.types t WITH(NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
LEFT JOIN (
     SELECT i.[object_id], ic.column_id, i.is_primary_key
     FROM sys.indexes i WITH(NOWAIT)
     JOIN sys.index_columns ic WITH(NOWAIT) ON ic.[object_id] = i.[object_id] 
          AND i.index_id = ic.index_id
     WHERE i.is_primary_key = 1
) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
LEFT JOIN sys.foreign_key_columns fkc WITH(NOWAIT) ON fkc.parent_object_id = c.[object_id] 
     AND fkc.parent_column_id = c.column_id
WHERE c.[object_id] = OBJECT_ID('TableName')

Devart · Accepted Answer · 2013-07-25T06:08:57.843

Try this one -

SELECT
      [Column Name] = c.name
    , [Data type] = t.name
    , [Max Length] = c.max_length
    , c.is_nullable
    , [Primary Key] = ISNULL(i.is_primary_key, 0)
    , IsFK = ISNULL(fkc.parent_object_id / fkc.parent_object_id, 0)
FROM sys.columns c WITH(NOWAIT)
JOIN sys.types t WITH(NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
LEFT JOIN (
     SELECT i.[object_id], ic.column_id, i.is_primary_key
     FROM sys.indexes i WITH(NOWAIT)
     JOIN sys.index_columns ic WITH(NOWAIT) ON ic.[object_id] = i.[object_id] 
          AND i.index_id = ic.index_id
     WHERE i.is_primary_key = 1
) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
LEFT JOIN sys.foreign_key_columns fkc WITH(NOWAIT) ON fkc.parent_object_id = c.[object_id] 
     AND fkc.parent_column_id = c.column_id
WHERE c.[object_id] = OBJECT_ID('Table_Name')

This gives me if column has a foreign key, but I need the name of the table for the foreign key. — runfastman, Jul 25 '13 at 13:28

What table a column is keyed to

2 Answers2