[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

How to identify particular table's particular column refer where?

In my DB I have a table called tAuditLog which has column TranID. I would like to get list of tables which refer this column as Forgine key.


Please note that the same column TranID could exist in some other table as well. I dont want to list them. Please assist.
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi.

You may want to look at INFORMATION_SCHEMA.KEY_COLUMN_USAGE view, or use the sys.foreign_keys / sys.foreign_key_columns view.

Kevin

EDIT: I copied from wrong SQL query.  The above views are what I meant.  I will post an example query in a moment in case you are not familiar with the system views.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an example as promised:
SELECT TableName = t.[name], ColumnName = c.[name]
FROM sys.foreign_key_columns fk
JOIN sys.tables t ON t.object_id = fk.parent_object_id
JOIN sys.columns c ON c.object_id = t.object_id 
 AND c.column_id = fk.parent_column_id
/* joins to filter referenced table and column. */
JOIN sys.tables tref ON tref.object_id = fk.referenced_object_id
JOIN sys.columns cref ON cref.object_id = tref.object_id 
 AND cref.column_id = fk.referenced_column_id
WHERE tref.[name] = 'tAuditLog' AND cref.[name] = 'TranID'
;

Open in new window

0
 
dbaSQLCommented:
SELECT
r.Table_Name,r.Column_Name
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
  ON u.CONSTRAINT_CATALOG = fk.UNIQUE_CONSTRAINT_CATALOG
  AND u.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA
AND u.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE r
    ON r.CONSTRAINT_CATALOG = fk.CONSTRAINT_CATALOG
    AND r.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
    AND r.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
WHERE u.TABLE_NAME = 'TABLE NAME'
  AND u.COLUMN_NAME = 'COLUMN NAME'
0
 
Kevin CrossChief Technology OfficerCommented:
Good example, @dbaSQL.  I must not have had enough coffee this morning as I listed the correct INFORMATION_SCHEMA view then edited it. *sigh*
0
 
dbaSQLCommented:
No worries.  We've all been there.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now