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.
Please note that the same column TranID could exist in some other table as well. I dont want to list them. Please assist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT
r.Table_Name,r.Column_Name
FROM
INFORMATION_SCHEMA.CONSTRA
ON u.CONSTRAINT_CATALOG = fk.UNIQUE_CONSTRAINT_CATAL
AND u.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEM
AND u.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRA
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'
r.Table_Name,r.Column_Name
FROM
INFORMATION_SCHEMA.CONSTRA
ON u.CONSTRAINT_CATALOG = fk.UNIQUE_CONSTRAINT_CATAL
AND u.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEM
AND u.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRA
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'
Good example, @dbaSQL. I must not have had enough coffee this morning as I listed the correct INFORMATION_SCHEMA view then edited it. *sigh*
No worries. We've all been there.
You may want to look at INFORMATION_SCHEMA.KEY_COL
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.