As a DBA, at one time or another, you will need to quickly find an object within a database, such as a table, view, or stored procedure. In other cases, it is handy to be able to search objects to locate specific text strings. Depending on which version of SQL Server you are using, you have had several choices.

For example, in SQL Server 2005/2008, I could run the following T-SQL code to find all of the tables in the AdventureWorks database that includes the string “Vendor”.

USE AdventureWorks

GO

SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM    INFORMATION_SCHEMA.TABLES

WHERE   TABLE_NAME LIKE ‘%Vendor%’

Or, if I wanted to find all the columns in the AdventureWorks database that include the string “Vendor”, I could run this T-SQL code.

USE AdventureWorks

GO

SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

FROM    INFORMATION_SCHEMA.COLUMNS

WHERE   COLUMN_NAME LIKE ‘%Vendor%’

Using INFORMATION_SCHEMA views to find specific object names (and related data) is very powerful and let’s you write many different queries to find a lot of information about your database’s objects. The only downside to doing this is that you have to write the T-SQL code to look for what you are searching for, and for databases with thousands of objects, the queries might run a little slow.

If you have SQL Server 2008, you have the option of using Management Studio’s (SSMS) built-in search feature. If you are not familiar with this feature, go to “View” from the main menu and select “Object Explorer Details”. If you look near the top of the screen, you will see a search box, as you can see below.

In the above screen shot, I have searched for “%Vendor%” from within the AdventureWorks database, and 17 matches were found. If I click on any of the objects, then a few details about the object are displayed at the bottom of the screen.

This is certainly an easier way to search for objects than writing your own T-SQL code, but the information it returns about each object, as you can see, is limited. Or, let’s say that I wanted to jump to one of the objects that was found in order to view it in more detail from within SSMS. Unfortunately, I can’t do this automatically. I must manually find the object using the SSMS Object Explorer, then open it up from there to view any details. The SSMS search feature also doesn’t have the ability to search for text strings within objects, which is often something that DBAs want to do. And if your database has thousands of objects, it might take a while for SSMS search to return all of the results.

Now we get to the main purpose of this article, and that is to talk about SQL Search, a new free searching tool from Red Gate Software. SQL Search has the ability to search for object names, or text strings within objects, directly from SSMS. The best way to learn what it can do is to show you, so let’s get started.

To install SQL Search, download the free tool from here, and then run the installer on any computer that has SQL Server 2005/2008 SSMS installed on it. Installation takes less than a minute, and is fast and easy.

Need to Know: The first time you use SQL Search, it will take several seconds, or a little longer if your server has lots of databases with lots of objects, to index them. Once it has done this, it can find any object or text string virtually instantly.

The next time you start SSMS, you will notice that a “SQL Search” button has been added to SSMS’s menu.

Whenever you need to quickly find any object, or text string, within a SQL Server instance, simply click on the button, and the SQL Search screen appears.

Using SQL Search is very easy. All you have to do is to enter whatever it is you are searching for into the search textbox and press “Enter.” Of course, before you do that, you may want to set some additional options first. For example, by default, any search string you enter will be assumed to be a wild card search. If you don’t want to perform a wild card search, but an exact match search instead, then click on the “Exact Match” checkbox.

To the right of the “Exact Match” option, you will notice that you can choose three more options that act as filters. The first option is used to specify the type of object, if any, that you want to search for.

As you can see above, you can choose to search all objects, or some subset of objects. If you know what type of object you are searching for, then using this option will help to filter out any data that is not useful to you.

After selecting the type of object you are looking for, you can also select which database you want to search by selecting it from the drop-down box.

As you can see above, not only can you search individual databases, you can also search all databases at once, if you want.

The last option is to select which SQL Server instance you want to search. You can search any SQL Server instance that is registered with SSMS.

Tip: I don’t know about you, but I often forget the entire name of an object, but I can remember part of it. Since SQL Search allows me to quickly search for any string, I can just enter the part of the object name I can remember, and then any object that matches is displayed. I can then quickly search through the results, and then quickly identify the name of the exact object I am looking for.

Now that we know about the available search options, let’s try a search. Like I did in my previous examples, let’s search for the string “Vendor” in the AdventureWorks database, and while we are at it, let’s go ahead and search all objects to see what SQL Search finds for us. When I perform this search, SQL Search find 43 instances where the string “Vendor” is found in an object name, or as text inside an object.

As you can see, a lot of search data is returned, including the database where the object is located, the object’s schema, the object’s name, the object’s type, and where the matches occurs on. While the first four columns of the results are obvious, the fifth column, “Matches on”, may not be so obvious.

If the result in the “Matches on” column is “Name”, then the match is on the object’s name. If the result is “Column”, the match is on a column inside an object. If the result is “text”, then the match was found on text inside an object. To see what I mean, notice in the above screen shot that I have highlighted the “vVendor” object in the top portion of the screen, which is a view, and notice that “Text” is in the “Matches on” column. If you look at the bottom of the screen, you will see the T-SQL code that was used to create the view, and highlighted in yellow is the word you are searching on.

Tip: If too much data is returned, you can always filter the data by changing the filter options at the top of the screen.

Now let’s say that we want to explore this object in more detail from SSMS. Instead of having to find the object manually, all I have to do is to double-click any object displayed in the results and I am brought directly to the object from within the SSMS Object Explorer, as you can see below.

Now that I am at the object, I can easily use any of the features available to me from within SSMS to explore or work with the object.

Once you are done using SQL Search, just close the window and it is gone and out of your way.

And that’s it. That’s all you need to know how to use SQL Search. As you can see, not only is it a powerful tool, it is incredibly easy to use. Given that the tool is free, I highly recommend all DBAs to download it and give it a try.