SELECT  OBJECT_NAME(Object_id) ,
        OBJECT_NAME(referenced_major_id) ,
        name AS columnname
FROM    sys.sql_dependencies
        OUTER APPLY ( SELECT    name
                      FROM      sys.columns
                      WHERE     column_id = referenced_minor_id
                    ) s

quote:

---

Originally posted by Vishal_sql

i am not getting correct result using sys.sql_dependencies and joining with column_id = referenced_minor_id

when i see the OBJECT_NAME(sd.referenced_major_id), it returns NULL,sysrowsets,sysrscols

I tried using below query but most of the Object Name does not have column Ststus which i am searching for...

SELECT distinct
sm.name,
OBJECT_NAME(sd.Object_id) AS ObjectName,
so.type_desc,
OBJECT_NAME(sd.referenced_major_id) AS TableName,
s.name AS ColumnName

FROM sys.sql_dependencies sd
inner join sys.objects so on sd.object_id=so.object_id
inner join sys.schemas sm on sm.schema_id=so.schema_id
OUTER APPLY ( SELECT name
FROM sys.columns
WHERE column_id = referenced_minor_id
) s
where sd.referenced_major_id=1204967419
and s.name='status'

---

SELECT OBJECT_NAME(Object_id) AS Name,
description
FROM sys.sql_modules
WHERE definition LIKE '% yourcolumnname %'

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Vishal_sql

i am not getting correct result using sys.sql_dependencies and joining with column_id = referenced_minor_id

when i see the OBJECT_NAME(sd.referenced_major_id), it returns NULL,sysrowsets,sysrscols

I tried using below query but most of the Object Name does not have column Ststus which i am searching for...

SELECT distinct
sm.name,
OBJECT_NAME(sd.Object_id) AS ObjectName,
so.type_desc,
OBJECT_NAME(sd.referenced_major_id) AS TableName,
s.name AS ColumnName

FROM sys.sql_dependencies sd
inner join sys.objects so on sd.object_id=so.object_id
inner join sys.schemas sm on sm.schema_id=so.schema_id
OUTER APPLY ( SELECT name
FROM sys.columns
WHERE column_id = referenced_minor_id
) s
where sd.referenced_major_id=1204967419
and s.name='status'

---

did you try

SELECT OBJECT_NAME(Object_id) AS Name,
description
FROM sys.sql_modules
WHERE definition LIKE '% yourcolumnname %'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

quote:

---

Originally posted by James K

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Vishal_sql

i am not getting correct result using sys.sql_dependencies and joining with column_id = referenced_minor_id

when i see the OBJECT_NAME(sd.referenced_major_id), it returns NULL,sysrowsets,sysrscols

I tried using below query but most of the Object Name does not have column Ststus which i am searching for...

SELECT distinct
sm.name,
OBJECT_NAME(sd.Object_id) AS ObjectName,
so.type_desc,
OBJECT_NAME(sd.referenced_major_id) AS TableName,
s.name AS ColumnName

FROM sys.sql_dependencies sd
inner join sys.objects so on sd.object_id=so.object_id
inner join sys.schemas sm on sm.schema_id=so.schema_id
OUTER APPLY ( SELECT name
FROM sys.columns
WHERE column_id = referenced_minor_id
) s
where sd.referenced_major_id=1204967419
and s.name='status'

---

did you try

SELECT OBJECT_NAME(Object_id) AS Name,
description
FROM sys.sql_modules
WHERE definition LIKE '% yourcolumnname %'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

This will produce useful results in some cases, but in many cases it will produce useless results, and in other cases it will altogether miss references.

E.g. If your column name is "E", it is likely to produce a ton of results, most of which would be useless.

If your column is referenced with escape characters e.g., [MyColumnName], this will miss such references.

That said, I use this very often; I don't use single character column names. And, I run it with various styles, [colname], "colname", ' colname ', 'colname' etc. to catch anything that may have not been included. Still it is not a 100% reliable test.

---

quote:

---

Originally posted by Vishal_sql

i tried ur solution but it produces lot of useless data as james said.
James your solution was near but it too gives many useless results...

Does it mean we could not find the column dependency on objects in sql server ?

---