Find tables with foreign keys

 

-- Find tables with foreign keys 
-- Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server 
 
       SELECT 
              t.name TableWithForeignKey
       ,      fk.constraint_column_id FK_PartNo 
       ,      c.name ForeignKeyColumn 
       FROM   sys.foreign_key_columns fk
       JOIN   sys.tables t 
              ON     fk.parent_object_id = t.[object_id] 
       JOIN   sys.columns c 
              ON     fk.parent_object_id = c.[object_id] 
              AND fk.parent_column_id = c.column_id
       JOIN   sys.tables t1
              ON     fk.referenced_object_id = t1.[object_id]  
       ORDER BY
              TableWithForeignKey
       ,      FK_PartNo