?
Solved

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

Posted on 2013-12-18
5
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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 60

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 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