How to find whether an object is used by any sp , view ?

Question

Post reply

How to find whether an object is used by any sp , view ?

vignesh.ms

Hall of Fame

Points: 3456
More actions
November 19, 2013 at 7:26 am

#300283

How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
Thanks Many

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Hunchback SSCrazy Points: 2649 More actions · Answer 1

For version 2008 or greater check the following catalog views in BOL:

- sys.sql_expression_dependencies

- sys.dm_sql_referencing_entities

- sys.dm_sql_referenced_entities

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi

I found this in my repository

select 'TO', 'Object name' = (s6.name+ '.' + o1.name),

[Type] = substring(v2.name, 5, 16),

[Updated] = substring(u4.name, 1, 7),

[Selected] = substring(w5.name, 1, 8),

'Column' = col_name(d3.depid, d3.depnumber)

from sysobjects o1

,master.dbo.spt_values v2

,sysdepends d3

,master.dbo.spt_values u4

,master.dbo.spt_values w5

,sysusers s6

where o1.id = d3.depid

and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'

and u4.type = 'B' and u4.number = d3.resultobj

and w5.type = 'B' and w5.number = d3.readobj|d3.selall

and d3.id = OBJECT_ID('schema.your_sp_name')

and o1.uid = s6.uid

and deptype < 2

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Lowell SSC Guru Points: 323518 More actions · Answer 3

this seems to work for me;

I had this saved in my snippets:

SELECT depz.referenced_schema_name,

depz.referenced_entity_name,

objz.type_desc,

depz.referencing_id,

Object_schema_name(depz.referencing_id),

Object_name(depz.referencing_id),

colz.name AS ColumnName

FROM sys.sql_expression_dependencies depz

INNER JOIN sys.objects objz

ON depz.referencing_id = objz.object_id

LEFT OUTER JOIN sys.columns colz

ON objz.object_id = colz.object_id

AND colz.column_id = referencing_minor_id

WHERE referenced_id = Object_id(N'EDLogDetail');

--AND colz.name = 'EmployeeNumber'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

vignesh.ms

Hall of Fame

Points: 3456

November 19, 2013 at 10:41 am

#1667501

Thanks Lowell exactly what i need...

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I may be remembering incorrectly, but if you use dynamic SQL in your procedures, the dependency might not show up in the list. If you find that's the case, something like this will help you find those references.

select object_schema_name(object_id), object_name(object_id), definition

from sys.sql_modules

where definition like '%EDLogDetail%'

order by object_schema_name(object_id), object_name(object_id);

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 6

Ed Wagner (11/19/2013)
I may be remembering incorrectly, but if you use dynamic SQL in your procedures, the dependency might not show up in the list. If you find that's the case, something like this will help you find those references.
select object_schema_name(object_id), object_name(object_id), definition
from sys.sql_modules
where definition like '%EDLogDetail%'
order by object_schema_name(object_id), object_name(object_id);

good point Ed;

even that might not catch 100%, if some dynamic SQL was getting a tablename from say, sys.tables or from a paramters, and executing a command built from that.

SET @cmd = 'SELECT TOP ' + CONVERT(VARCHAR,@Top) + ' * FROM ' + QUOTENAME(@TblName) + ' ORDER BY 1 DESC '

EXEC(@cmd)

also , because of late binding, it's possible to create a procedure that references a table that doesnt exist at the time of procedure creation(yet);

in that case there's no references in the views unless you ALTER PROC and rebuild it with the same definition.

i try to check for invalid objects and rebuild just in case as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!