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.

2. Dynamic SQL Statement

Dynamic SQL Statements in objects (stored procedures, triggers, etc) that contain the text in plain.

CREATE PROCEDURE dbo.usp_DynamicSQLSimple
AS
/**************************************************************************
 *
 * Purpose: Update all NULL values to "Blank" on Title in dbo.Game.
 *			This is for a demo used in
 *			https://www.mssqlgirl.com/sql-tools-in-a-jiffy-sql-search.html
 *
 * Parameters: None
 *
 * Update History:
 * 20140209 - Julie Koesmarno - v1.0 - Initial Release
 *
 **************************************************************************/
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLStmt VARCHAR(1000);

	-- Construct a dynamic SQL Statement that updates the Column values.
	SET @SQLStmt = 'UPDATE [dbo].[Game] ' +
				   'SET [Title] = ''Blank'' ' +
				   'WHERE [Title] IS NULL '; 

	-- Execute SQL Statement
	IF @SQLStmt IS NOT NULL
	BEGIN
		PRINT @SQLStmt; 
		EXEC (@SQLStmt);
	END;

	-- Print number of rows updated.
	PRINT QUOTENAME(@@ROWCOUNT) + ' rows(s) updated';

END
GO

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.

CREATE PROCEDURE dbo.usp_DynamicSQLComplex 
AS
/**************************************************************************
 *
 * Purpose: Update all NULL values to "Blank" on first column that starts 
 *			with T in dbo.Game. 
 *			This is for a demo used in
 *			https://www.mssqlgirl.com/sql-tools-in-a-jiffy-sql-search.html
 *
 * Parameters: None
 *
 * Update History:
 * 20140209 - Julie Koesmarno - v1.0 - Initial Release
 *
 **************************************************************************/
BEGIN
	SET NOCOUNT ON;

	DECLARE @ColumnName VARCHAR(255);
	DECLARE @SQLStmt VARCHAR(1000);

	-- Get the first Column that starts with T in dbo.Game.
	SET @ColumnName = (SELECT TOP 1 COLUMN_NAME 
						FROM INFORMATION_SCHEMA.COLUMNS
						WHERE TABLE_NAME = 'Game'
						AND TABLE_SCHEMA = 'dbo'
						AND COLUMN_NAME LIKE 'T%');

	-- Print message
	PRINT ISNULL('First column in dbo.Game that starts with T is ' + @ColumnName, 
			'No column starts with T found in dbo.Game');

	-- Construct a dynamic SQL Statement that updates the Column values.
	SET @SQLStmt = 'UPDATE [dbo].[Game] ' +
				   'SET ' + QUOTENAME(@ColumnName) + ' = ''Blank'' ' +
				   'WHERE ' + QUOTENAME(@ColumnName) + ' IS NULL ' ;

	-- Execute SQL Statement
	IF @SQLStmt IS NOT NULL
	BEGIN
		PRINT @SQLStmt;
		EXEC (@SQLStmt);
	END;

	-- Print number of rows updated.
	PRINT QUOTENAME(@@ROWCOUNT) + ' rows(s) updated';

END
GO

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:

On a SQL Quest Using SQL Search by Red Gate by Mickey Stuewe

Headache + Pain <> Red Gates SQL Search by Chris Yates

How SQL Search saves you time by Jeffrey Verheul