Post reply

Searching for constructive criticism/feedback on a "SQL Search"-ish proc I wrote

  • January 15, 2020 at 4:10 am

    #3715489

    #roastme? lol...kidding...

    tl;dr: I wrote a proc that basically does the same thing as Redgate SQL Search. Just curious to see if anyone wants to look at it and maybe provide some feedback...

    Disclaimer: I've been working on this proc off and on for years. It's something I regularly use tons of times per day, it has been a massive help to me in my daily tasks. I start with that because many people might (and regularly do) question why I wrote this when there are GUI's (such as Redgate SQL Search). There may (and are) better things out there...but I like using it, and it works for me...so, let me have this one 🙂 lol

    Another big part of me starting this project was that it's a way of forcing me to work on various development tasks and learn new things. I started writing this when I was VERY new to SQL, so it's actually helped me learn quite a bit over the years.

    Anyways...I thought maybe I would post it up here and see if anyone would be interested in playing around with it themselves, maybe look through the code to provide better ways to do things, or maybe even suggest some features I hadn't thought of.

    Here's the file on GitHub:

    https://github.com/chadbaldwin/SQL/blob/master/dbo.usp_SchemaSearch.StoredProcedure.sql

    99.9% of the time I use it, this is what I run:

    EXEC dbo.usp_SchemaSearch
        @Search = 'SearchCriteria', @ANDSearch = NULL, @ANDSearch2 = NULL, @WholeOnly = 1
        , @DBIncludeFilterList = NULL, @DBExcludeFilterList = NULL
        , @SearchObjContents = 1, @FindReferences = 1
        , @BaseFilePath = 'X:\Git\database'
        , @SuppressErrors = 1

    Here's the ReadMe I have posted on GitHub to give a description of its features....

    • Multiple search criteria (@Search, @ANDSearch, @ANDSearch2)

      • (AND joins only)...search "this" AND "that"

    • Partial and exact match option (@WholeOnly)

      • If @WholeOnly = true, similar to "match whole word", applies to all search parameters

    • Searches all object types...triggers, functions, procs, views, etc
    • Output the physical file path of the item (@BaseFilePath)

      • This makes it easy to quickly open the actual file
      • Example CTRL+C, CTRL+O, CTRL+V, Enter...I have a keyboard macro set up for it
      • As of now...it only supports one convention for file paths and names, which is the one I use...I hope eventually, I can add some functionality to make that configurable.

    • 2nd level of depth (@FindReferences)

      • Ability to find all the references to your search results
      • Example...you search for a keyword, the proc will return all procs that contain that keyword. You can then go one level deeper and enable @FindReferences, this will find all objects that reference those objects.

        • Limitations - only finds references for stored procedure results, and it only searches other stored procedures and job steps

    • Result caching (@CacheObjects, @CacheOutputSchema)

      • Many times if you are using this heavily to trace a large process, you may not want to hit every database to grab every object every time you run this proc. So there is an option to cache all objects into a temp table. This way each run, only the temp table is searched. You can also query the temp table manually for more complex searches of your own.

    • DB filter lists (@DBName, @DBIncludeFilterList, @DBExcludeFilterList)

      • The ability to provide both and inclusion and exclusion list of database name filters (using LIKE syntax, comma delimited)
      • For example...if you want to exclude all databases with '%test%,%backup%'. That will exclude all databases that have "test" or "backup" in the name
      • @DBName - included for simplicity / legacy but will likely be removed eventually. It's an exact match parameter. Filters the proc to only look at the one database, and is an exact match.

    • This topic was modified 4 years, 3 months ago by  chad 62627.
    • This topic was modified 4 years, 3 months ago by  chad 62627.
    • This topic was modified 4 years, 3 months ago by  chad 62627.

  • January 16, 2020 at 5:10 am

    #3715879

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

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

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