Main menu

SQL Tools Review: SQL Search

As a SQL Server database developer / DBA / consultant, at some point in your daily routine, you’d probably be asked to investigate a bug or to make an enhancement that is specific to a particular object. A common example is to remove a column from a table, and find all objects that refer to that column. Obviously you want to remove the references to the column (including in dynamic SQL statements*) before you drop that column from the table.

An easy way to find out all the objects in the Server that may refer to that column is by using SQL Search tool by Red Gate.

Price

It doesn’t cost a dime. At the time of writing, SQL Search 1 is free for download.

Easy to use

The tool is an add-in to SQL Server Management Studio. The user interface is self-explanatory.

It’s at most 4 simple steps:

1. Type in the text that you want to search

2. Specify whether it’s an exact match or not

3. Choose which object you want to search and on

4. Specify the server. The server drop down list is populated based on the database servers that you have connected on SSMS in Object Explorer.

Limitations

1. Windows Azure SQL Database

If you look carefully in the screenshot above, there are a couple of hints that show I have 2 connections to Windows Azure SQL Database (WASD) on my SSMS Object Explorer. You are right. Unfortunately, searching on WASD instances is not supported in SQL Search 1. Hopefully Red Gate will add this feature in.

On the contrary, if you have a stored procedure with a dynamic SQL statement that constructs / resolves a reference to a text that you are searching, during execution, SQL Search will not be able to find it. Suppose you are interested in updating a first column in dbo.Game where its name starts with “T” and has a NULL value. Obviously, if “Title” is the first column that starts with T in dbo.Game, the dynamic SQLStatement in the below stored procedure (dbo.usp_DynamicSQLComplex) will be the same as above (dbo.usp_DynamicSQLSimple)..

Below is the stored procedure example on a more complex dynamic SQL Statement, where @SQLStmt only contains a reference to “Title” at the time of execution. Therefore SQL Search is not going to be able to find it, which is fair enough, but it is something that you have to bear in mind when finding dependencies.

Alternatives

Another alternative is to create your own script that inspects sys.sql_modules and iterate through your instances. You will then need to look up the object_id to get more details on them. So, for a free tool, SQL Search is much easier to do. There is more than one way to skin a cat, but using SQL Search is a user friendly and time saving way.

Wrap Up

I think SQL Search is one of a must have tool for database developers. You may not use it on your daily basis, but boy, when you do need it, it really saves you time, especially if you don’t have your own reusable custom script to search text.

Further Reading

Not convinced on how SQL Search can help you out? Check out other #SQLCoop blog posts here: