How to find the objects that use a stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » How to find the objects that use a stored...

How to find the objects that use a stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

skp_01702

Posted Wednesday, September 03, 2008 12:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 07, 2010 1:14 PM
Points: 8, Visits: 21

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.

Post #563312

Roy Ernest

Posted Wednesday, September 03, 2008 12:54 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 3,280, Visits: 6,622

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

Post #563321

Matt Cherwin

Posted Wednesday, September 03, 2008 12:54 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 1:47 PM
Points: 396, Visits: 241

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

Post #563322

Ninja's_RGR'us

Posted Wednesday, September 03, 2008 12:58 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 21,357, Visits: 9,537

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

Post #563327

skp_01702

Posted Wednesday, September 03, 2008 3:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 07, 2010 1:14 PM
Points: 8, Visits: 21

Thanks to All for Simple solutions... :)

Post #563426

Grant Fritchey

Posted Thursday, September 04, 2008 5:44 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #563699

Matt Cherwin

Posted Thursday, September 04, 2008 7:37 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 1:47 PM
Points: 396, Visits: 241

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

Post #563804

Grant Fritchey

Posted Thursday, September 04, 2008 7:44 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #563810

« Prev Topic | Next Topic »

Permissions