September 3, 2008 at 12:41 pm
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.
September 3, 2008 at 12:54 pm
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
September 3, 2008 at 12:54 pm
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
September 3, 2008 at 12:58 pm
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
September 3, 2008 at 3:26 pm
Thanks to All for Simple solutions... 🙂
September 4, 2008 at 5:44 am
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
September 4, 2008 at 7:37 am
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
September 4, 2008 at 7:44 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply