How to find the objects that use a stored procedure

  • 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.

  • 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

  • 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

  • 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

  • Thanks to All for Simple solutions... 🙂

  • 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

  • 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

  • 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