Introduction
I'm a DBA in the financial sector, managing a 4-member team of DBAs responsible for 1492 databases, spread across four environments (Development, QA, UAT/Staging and Production) and 90 servers. When managing this many servers and databases, we need an efficient way to search for objects in a particular database, or in a particular SQL Server instance, or even for particular strings within the object metadata. After all, even the most dedicated team will likely struggle to memorize every schema and every line of code in every database object!
Very limited searching on object name is available within SSMS Object Explorer (click F7 to open the search bar), but we need more than that. We might try the 'brute force' approach of scripting out the database schema into a text editor that has good string searching capabilities, but then often we want to search other objects too, such as SQL Server Agent jobs, SSIS Packages and so on. With a bit of patience and some hand-rolled scripts and stored procedures, Phil Factor has proved that there is quite a lot of information we can drag out of the Information Schema and System Catalog views – see Exploring SQL Server table metadata with SSMS and TSQL and Exploring you Database Schema with SQL.
However, for sheer ease of use, the tool I reach for, when I need to perform a detailed search, is Red Gate's SQL Search Utility, a free plug-in for SSMS. It allows me to search the object metadata across multiple object types and databases and has saved me many hours of time and a lot frustration.
A Typical Quest Begins: Hunting Down an Agent Job
During a recent, typical day at work, I was tasked with implementing a maintenance job on one of our servers. I knew that a similar job existed already and I could recall a portion of its name, and even the job step name, but the server hosted a mind-boggling number of jobs and, for the life of me, I couldn't find the one I wanted.
Frustration started to set in, and I was just about to give up and rewrite the job from scratch when I recalled reading somewhere that the latest version of SQL Search had expanded its search capabilities to include jobs.
It took a few seconds to download and install the upgrade to my current version, and then I opened up SSMS and fired up SQL Search (I am demo'ing this use case on my own local SQL Server instance rather than a Production instance!).
Figure 1
Next, I had to define my search. I knew a phrase in my job name but didn't know the whole name, so I typed in what I knew, without selecting the "Exact match" search option.
Figure 2
Next, I clicked on the All Object Type drop down to reveal, beneath the list of searchable database objects, the Server Object called jobs. Even though I wanted only jobs, I was intrigued to see what else it would find, so I went ahead and checked them all.
Figure 3
After a few moments, SQL Search returns my results:
Figure 4
In the Object Name column, I see my job name, and the Detail column even provides the actual job step detail!
Since then, I've used this job search on numerous occasions. A quick search on a keyword will reveal job steps associated with, for example, SSIS package execution, far quicker than drilling down into the Agent to review each individual step.
What Else is Possible?
Most often, I use SQL Search to track down all occurrences of a specific schema object, or column in a table, or to find the whole set of schema objects related to a particular business unit.
For example, we'll be working with a vendor on a process flow for an upgrade and will need to review all schema objects related to that specific business unit. Since each business unit uses strict naming conventions for their business objects, a quick search on "lawyer", say, will return all of the tables, views, stored procedures related to this unit.
Other times, a developer will approach us with a tuning or maintenance question with regard a particular stored procedure, but won't necessarily know in which of the 1500 databases it is located. Fortunately, a DBA will know the server on which to look and, armed with SQL Search, will locate quickly any stored procedures with the name provided.
Another good use case is hunting down occurrences of "troublesome" strings in our database code base. Many years ago, we didn't have source control validation to check for issues such as /**To do…**/ comments in the code, or use of SELECT *. In a code base of the size we manage, they still crop up from time to time. However, they are becoming fewer and fewer, since a quick SQL Search on "*To" will hunt them down quickly. The latest version of SQL Search, which also introduces ability to search with % wildcards as well as Boolean operators, makes these sorts of strong searches even more powerful.
Wish List
In our organization, we have a Central Management Server (CMS) that segregates the databases by environment (production, non-production), so I can open the Production directory, for example, all the servers in that category open up in SSMS Object Explorer, and I can use SQL Search to search for items on each one.
A nice additional capability would be if SQL Search could search against multiple selected servers in the object explorer, rather than just one at a time. For example, we have a standard DBA database on every server; the ability to perform a quick search on this database, across multiple servers, would be very useful.
Summary
When managing a lot of database servers, and even more databases, we need tools and automation to help us do our job efficiently. SQL Search is a simple tool that helps us track down specific objects, or fragments of SQL within an object, or all objects that belong to a particular business function, and so on. It is a simple tool, but often it's the simple tools that we turn to most often, in our day to day jobs.
