list User Stored Procedures that reference a specified table

  • Greg,

     

    Thanks for a great piece of code!

    Running the following, I identified that my longest stored procedure spans 4 rows:

    SELECT NAME, c FROM SYSOBJECTS S

    INNER JOIN

    (

    select id, count(*) AS c from syscomments

    group by id

    having count(*) > 1

    )  ABC

    ON S.ID = ABC.ID

    WHERE NAME NOT LIKE ('sys%')

    and name not like ('qs_%')

    ORDER BY c desc

     

    I am currentlly trying to figure out how to extend it to identify sps spanning more than 2 rows.

     

    Thank you

  • The funny thing is, the query works even when the string I'm searching for is in the 5th row in syscomments.

    Greg

    Greg

  • Greg,

    The query failed when I tried to run it agaist one of our smaller client DBs -

    Server: Msg 510, Level 16, State 2, Line 1

    Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

    Regards,

    Harley

  • strange ... it worked for me fine.

     

    Try the following: Add OPTION ROBUST PLAN as suggested somewhere here:

    http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040e14-errors.html

     

Viewing 4 posts - 16 through 18 (of 18 total)

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