Recently, I started diving into my utility belt. There are tools that I’ve had at my disposal for some time, some that I have tried and not gone back to, and ones that I use everyday. This has a definite potential to start exploring these utilities over the next couple of months and one that I touched on last few weeks that I want to expound a bit more on today is Red Gates SQL Search utility.
I’ve come accustomed to this utility as being my quick hit Sherlock Holmes style of investigating all sorts of issues that a common data professional may encounter throughout the work day.
SQL Search is a “free” tool that is an SSMS add-in that pretty much allows you to scan across all databases for a plethora of information for all objects. How do you access this utility?
Once downloaded and installed within SSMS you will find it on your tool bar as indicated below:
By clicking on the SQL Search icon or by utilizing the hot key of (CTL+ALT+D) you will open the SQL Search Tab in your current listed session below:
The Search Begins
Once you have this open I’d like to point out a few items.
- In the last image shown you will notice that in my Object Explorer I have my (local) instance selected and highlighted. If I had multiple instances in my Object Explorer SQL Search would open the tab on whichever instance I had highlighted.
- I can now begin my search. I have multiple options at my disposal on what I can check for and I can set limitations or filters if you will by selecting what type of object I’d like to filter on as noted below:
- The last two options are self explanatory with what database you are searching in, but I do want to point out the last option where I have (local) in my drop down. Currently, I do not have a second instance fired up in my Object Explorer; if I did then you could click this drop down and hop over to the other instance. This feature is offers nice compatibility when you have to work with multiple instances at once time.
By Jove Watson We’ve Found It
Now here is where the magic begins. For example, I know that I have a table called Release but I cannot remember where I saw it at, or maybe you’ve been in a meeting before and you leave with schema names but you’ve never seen the lay of the land before. Well here you go; if I type in the term Release and set it for all objects, scanning all databases my results are below:
…but it gets better. If you look at the link that says “Select object in Object Explorer” it will take you directly to the location inside the DB where the object is located for review
Fancy but now what?
SQL’s version of Scotland Yard says this is all well and good but can it do more? Let’s step back and think about this a moment. The whole goal, with any business I hope, is to make processes and procedures more efficient. This utility makes a strong case for the data professional to add this to their arsenal. Here are some real examples on what I utilize this tool for that haven’t been mentioned thus far:
- Off and on code reviews are done. Have you ever ran across the infamous /* TO DO */ comment? Heck I’ve put those comments in before in my own sp’s noting I need to finish something at a later time. I can quickly scan a DB and easily find these gaps in seconds
- Select *; don’t fool yourself. While these are frowned upon they are still used. Utilizing this tool has helped me pin point such code in seconds which then proceeds my conversation with the author of the code or update
- I’ve utilized this utility as my central hub for branching out when I know parts of names to objects. Gives me quick insights into the schematics of the database and what dependencies objects have prior to making any changes
- Efficiency – this one word carries so much weight. I wish I had this utility when I was first starting out; for me this product is definitely a game changer and did I mention it is free? Why not take advantage of it > go check it out now you know you need it in your data professional tool belt
Are You Using SQL Search Already?
If you already are utilizing this utility then drop Red Gate a line and let them know what your thoughts are on the subject; Red Gate always enjoys getting feedback and I have never seen a more thorough company in taking use cases and every day occurances by the user and figuring out how they can make their products better.
More utilities from Red Gate
If you like what you’ve seen thus far; than you haven’t seen anything yet. This will become a mini series I’m proud to work on with some other individuals that are part of the Red Gate family. To see their twist please check out: