Solved

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

Posted on 2013-12-18
5
293 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now