How can I get a list (result set of rows) of the table name and column name in each table that references primary key ID in table Addresses?

Question

Using SQL Server 2012, I want to mark a record inactive. Before I do, I want to check if its primary key is referenced.

I tried searching on "get a list of the table name and column name in each table that references primary key" and “get column name in each table that references primary key” but nothing relevant showed up. This is a tricky question to search on (for me).

Possible duplicate of [How to find foreign key dependencies in SQL Server?](https://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server) — Stan Shaw, Jul 07 '17 at 18:05
@sta -I was going to delete this question, but SO penalizes the deletion of questions that have been answered (I believe). — Ashwin Nirmul, Jul 11 '17 at 17:38
Fair enough - but with only 3 points, I can't imagine it's going to do irreparable harm... :) — Stan Shaw, Jul 11 '17 at 17:40

Karel-Jan Misseghers · Answer 1 · 2017-07-11T18:10:36.047

0

EDIT: Adding a query to list all foreign keys, tables that reference to your table, and columns in those tables that reference to it.

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE tab2.name = 'TABLENAME'

Query based on a reply in this thread; How can I list all foreign keys referencing a given table in SQL Server?

edited Jul 11 '17 at 18:10

answered Jul 07 '17 at 18:15

Karel-Jan Misseghers

797
8
31

ba.name is unbound. – Ashwin Nirmul Jul 07 '17 at 19:43
This query does not provide the foreign key column name. – Ashwin Nirmul Jul 07 '17 at 20:14
The query only checks for tables with the column. As stated on top of the answer, using sp_help (=F1 in SSMS), you can see all foreign key dependencies, so you can see which tables are dependant, or if your table is dependant on other tables. – Karel-Jan Misseghers Jul 07 '17 at 20:27
I see the problem with my question now. I updated it to request a result set. Thank you, @Karel-Jan. – Ashwin Nirmul Jul 11 '17 at 17:17
I have changed my reply, and added a query which, I think, answers your question. – Karel-Jan Misseghers Jul 11 '17 at 18:10

How can I get a list (result set of rows) of the table name and column name in each table that references primary key ID in table Addresses?

1 Answers1