Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query help

Author  Topic 

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-10-15 : 10:00:45
How to find all the sp or objects using a specific column of specific table in specific database in sql server 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 10:38:02
You can use sys.sysdepends or sys.sql_dependencies etc. For example like this:
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
Look at other columns in those system views if you need additional ifnormation.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:15:02
see

http://visakhm.blogspot.in/2012/03/advantages-of-using-syssqlmodules-view.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-10-17 : 08:12:54
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 08:23:48
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
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 08:28:22
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 08:30:46
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.


The other case is also not foolproof enough
For example if you use column inside dynamic sql string it wont report that.
I used this most often of course trying out few variant options

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 08:34:03
People who use dynamic SQL deserve that punishment!!
Go to Top of Page

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-10-17 : 09:07:57
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 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 10:03:06
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 ?


As suggested you might have to try a few variants before you get a closer result. It will just give you start

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -