Solved

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

12 Experts available now in Live!

Get 1:1 Help Now