June 29, 2009 at 11:50 pm
Hi Friends
I have table tDept
DeptNo DName Location
10 xyz abc
20 qwe yui
30 poi mnh
one more table tEmp
EmpId Name DeptNo
101 ---- 10
102 ---- 20
103 ---- 20
104 ---- 30
here Deptno is foreign key is Referenced by Dept table.
in my database this deptno columns is Referenced so many places, now i want Check the Query like
How to know Deptno-10 is Referenced in other tables.
June 30, 2009 at 8:13 am
With this code you will get a list of all tables with a column name of "DeptNo".
select
sys.tables.name as TableName
, sys.columns.name as ColumnName
, sys.columns.column_id
from
sys.tables
inner join sys.columns
on sys.tables.object_id = sys.columns.object_id
where
sys.columns.name = 'DeptNo'
Using other system tables (like sys.objects) you can also search for the string "DeptNo" in stored procedures, etc.
June 30, 2009 at 8:55 am
The only way you can find out if the value of a column is used in a child table is to query the child table(s) for the value.
In your example you could do a series of left joins or unions and left joins to get find out. Using your example tables this would show the DeptNo's not used in the tEmp table:
SELECT
'tEmp',
D.DeptNo
FROM
tDept D LEFT JOIN
tEmp E ON
D.DeptNo = T.DeptNo
WHERE
E.DeptNo IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply