How to find the objects that use a stored procedure

Question

Post reply

How to find the objects that use a stored procedure

skp_01702

Old Hand

Points: 376
More actions
September 3, 2008 at 12:41 pm

#212061

Hi,
I have a stored procedure "usp_SignOFF_Count". But do not know where it is called from. Its a huge database with hundreds of stored procs and functions. Is there a way to find out all the objects within the database that use it ( procedure/function Body)?
Thanks,
Sai.

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Try this.

Select [Text] from syscomments where [Text] like '%spInsert%'

You will get teh whole body of the SP. If you want just the name, then link the Object ID to get the name from SysObjects.

-Roy

Matt Cherwin Ten Centuries Points: 1313 More actions · Answer 2

I'm not 100% certain, but my first pass at this would be querying against the definition field in the sys.all_sql_modules view in the DB in question. Something like:

select ao.name

, asm.object_id

, asm.definition

from sys.all_sql_modules asm

inner join sys.all_objects ao on asm.object_id = ao.object_id

where asm.definition like '%usp_SignOFF_Count%'

______
Twitter: @Control_Group

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Was too slow... but just for the fun of it :

SELECT OBJECT_NAME(C1.id) AS Obj FROM sys.syscomments C1 LEFT OUTER JOIN sys.syscomments c2 on C1.id = C2.id AND C1.Colid = C2.Colid - 1 where CONVERT(VARCHAR(MAX), C1.text) + CONVERT(VARCHAR(MAX),ISNULL(C2.Text, '')) like '%usp_SignOFF_Count%' ORDER BY Obj

skp_01702 Old Hand Points: 376 More actions · Answer 4

skp_01702

Old Hand

Points: 376

September 3, 2008 at 3:26 pm

#866828

Thanks to All for Simple solutions... 🙂

Grant Fritchey SSC Guru Points: 398680 More actions · Answer 5

This is from BOL. I'd suggest trying it.

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent

FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Cherwin Ten Centuries Points: 1313 More actions · Answer 6

Grant Fritchey (9/4/2008)
This is from BOL. I'd suggest trying it.
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');

I had never heard of sys.dm_sql_referencing_entities before - that's fantastic. Thanks!

______
Twitter: @Control_Group

Grant Fritchey SSC Guru Points: 398680 More actions · Answer 7

Oops. Serious egg on my face. That's new for 2008. It won't work with 2005. Sorry.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning