Find all SPs, FNs, and VWs where particular string occurs

  • I have this task to find all SPs, FNs, and VWs where particular string occurs in definition of the object. In 300 databases in a huge server.

    How do you suggest that I go about it?

     

     

     

    Likes to play CHESS

  • Redgate's SQL Search?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Any non 3rd party suggestions?

    Likes to play CHESS

  • Assuming all of the DBs are in source control, you can do a simple string search.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • No source control.... It's a super secret, super ad hoc server...

     

    Likes to play CHESS

  • Redgate's SQL Search is the best answer - and it is free.  If you - for some reason - cannot use that tool then you are going to have to write the code to search the appropriate system tables, and run that code against each database.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • That's what I am doing right now, yes.

    And thank you, let me make s strong case for Redgate  SQL Search with my mgmt ...

    Likes to play CHESS

  • SELECT OBJECT_NAME(object_id) AS object_name, OBJECTPROPERTYEX(object_id, 'BaseType') AS object_type

    FROM sys.sql_modules

    WHERE definition LIKE N'%<string_you''re_looking_for>%'

    ORDER BY 2, 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Vladimir Isaev wrote:

    I have this task to find all SPs, FNs, and VWs where particular string occurs in definition of the object. In 300 databases in a huge server.

    How do you suggest that I go about it?

    Scott's answer is similar to what I was going to suggest.

    With that, I have to ask, why is it that you need to find a certain string in virtually every programmable object you have?

    Also, do you need to check on computed columns as well?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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