Solved

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

Posted on 2013-12-18
5
297 Views
Last Modified: 2013-12-19
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
Comment
Question by:Easwaran Paramasivam
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39726389
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39726414
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39726731
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39726776
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39726823
No worries.  We've all been there.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question