-1

How can I have a list of all columns in other tables/schemas that refer to a certain column A as foreign key ?

Kenny
  • 1,902
  • 6
  • 32
  • 61
  • 1
    right click the master table and select "view dependancies", which will list all related tables that link to the primary key. – Tanner Sep 16 '16 at 10:20
  • 1
    Possible duplicate of [How can I list all foreign keys referencing a given table in SQL Server?](http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) – Thomas G Sep 16 '16 at 10:22

1 Answers1

0

Something like this?

SELECT
    fk.Name,
    'Referenced table' = refTbl.Name,
    'Parent table' = parentTbl.Name,
    'Parent column' = c.name
FROM 
    -- FK constraint
    sys.foreign_keys fk
INNER JOIN 
    -- Referenced table (where the PK resides)
    sys.tables refTbl ON fk.referenced_object_id = refTbl.object_id
INNER JOIN 
    -- Parent table (which has the foreign key column)
    sys.tables parentTbl ON fk.parent_object_id = parentTbl .object_id
INNER JOIN 
    -- link to the columns involved in the FK contraint
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    -- column in the parent table that's part of the FK constraint
    sys.columns c ON c.object_id = parentTbl.object_id AND c.column_id = fkc.parent_column_id
WHERE
    refTbl.name = 'YourTableNameHere'
ORDER BY
    fk.Name, parentTbl.name
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459