Post reply

how to find all stored procs using a table, but ignore comments?

  • February 12, 2018 at 1:30 pm

    #403108

    hi.
    we have 1 server with multiple databases.. The data tables are mostly inside database A.. and there are at least several hundreds of stored procedures in databases B,C,D.  We want to find out all stored procedures referencing certain table . 
    However, we want to ignore anything in comment in the stored procedure.  ( unfortunately, a great amount of the stored procs have a lot of comments containing table names may not be in use in the real query.)
    Also, another issue is, the table name we are trying to search may be a SQL keyword, and is very common in queries ( for example, one of the table name we try to match is 'CASE' )
    Therefore, we can't just search the pattern in the definition of stored procs. Otherwise, we will end up with a lot of SPs which are actually not referencing the table we want to find.

    Is there a way to search all databases stored proc but ignoring all comments?

    thanks,

  • February 12, 2018 at 1:41 pm

    #1979517

    SELECT *
    FROM sys.dm_sql_referencing_entities(<YourTableNameHere>, 'OBJECT')

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 12, 2018 at 1:53 pm

    #1979518

    Jane777 - Monday, February 12, 2018 1:30 PM

    hi.
    we have 1 server with multiple databases.. The data tables are mostly inside database A.. and there are at least several hundreds of stored procedures in databases B,C,D.  We want to find out all stored procedures referencing certain table . 
    However, we want to ignore anything in comment in the stored procedure.  ( unfortunately, a great amount of the stored procs have a lot of comments containing table names may not be in use in the real query.)
    Also, another issue is, the table name we are trying to search may be a SQL keyword, and is very common in queries ( for example, one of the table name we try to match is 'CASE' )
    Therefore, we can't just search the pattern in the definition of stored procs. Otherwise, we will end up with a lot of SPs which are actually not referencing the table we want to find.

    Is there a way to search all databases stored proc but ignoring all comments?

    thanks,

    Do you have dynamic SQL? If you don't, then the function sys.dm_sql_referencing_entities might help you. If you do, you might miss some references.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 3 (of 3 total)

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