Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find the objects that use a stored procedure Expand / Collapse
Author
Message
Posted Wednesday, September 3, 2008 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 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
Posted Wednesday, September 3, 2008 12:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:55 AM
Points: 2,361, Visits: 6,750
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
Posted Wednesday, September 3, 2008 12:54 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:54 PM
Points: 396, Visits: 246
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
Posted Wednesday, September 3, 2008 12:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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
Posted Wednesday, September 3, 2008 3:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 1:14 PM
Points: 8, Visits: 21
Thanks to All for Simple solutions... :)
Post #563426
Posted Thursday, September 4, 2008 5:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 13,925, Visits: 28,319
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 Query Performance Tuning
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
Posted Thursday, September 4, 2008 7:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:54 PM
Points: 396, Visits: 246
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
Posted Thursday, September 4, 2008 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 13,925, Visits: 28,319
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 Query Performance Tuning
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 »

Add to briefcase

Permissions Expand / Collapse