Searching SQL Server for a word or phrase across all objects

So you know what the column is called, or what the table is called, but you just don't know where to find it on your SQL Server? This is my solution for searching throughout SQL Server for a particular string, it will run on 2005, 2008R2 and 2012.

First we need to define some variable and establish where we are going to look. The below code gets the variables we need created, and defines a cursor that will gather the names of all the databases on the server.
Then we need to cycle through these databases and do our search on each one. The following dynamic SQL iterates through the databases and performs the search on each.

	DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000)
	SELECT @search_str = 'tablename'
	DECLARE curDB
	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	OPEN curDB
	FETCH NEXT FROM curDB INTO @db_nm
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	SELECT @sql_str = 'SELECT distinct db_nm='''+@db_nm+''', OBJECT_NAME=o.name FROM '+@db_nm+'..syscomments s (NOLOCK)
	JOIN '+@db_nm+'..sysobjects o (NOLOCK) ON s.id = o.id
	WHERE text LIKE ''%'+@search_str+'%''
	SELECT TABLE_CATALOG as ''DATABASE'', TABLE_NAME, COLUMN_NAME
	FROM '+@db_nm+'.INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE ''%'+@search_str+'%'' or TABLE_NAME LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	FETCH NEXT FROM curDB INTO @db_nm
	END
	CLOSE curDB
	DEALLOCATE curDB

view raw early_stage_search_script.sql hosted with ❤ by GitHub

And there you have it, but how can we improve on this? by eliminating the cursor? by using a table (temporary, table variable or CTE) to store our result set? Yes of course, but this works and will get you on your way. As an extra result, lets add a search that checks SQL Agent jobs as well, see below for that code:

	SELECT @sql_str = ' SELECT j.name, s.step_id, s.step_name, s.command
	FROM MSDB..sysjobsteps s JOIN MSDB..sysjobs j ON s.job_id = j.job_id
	WHERE s.command LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)

view raw sql_agent_job_search.sql hosted with ❤ by GitHub

So now you will get many different result sets, and if you have more than 100 databases on your server you will exceed the maximum result sets that SSMS will display in 2008R2, so what to do? Lets store the results in a table and display them with a SELECT statement, by doing this we can also eliminate the empty result sets. Working fine? If you're getting access errors one small change will address that, lets only look in the databases you actually have access to by adding this:

	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	GROUP BY name HAVING HAS_DBACCESS(name) = 1 --funtion checks your access to each datbase

view raw dbaccess_checker.sql hosted with ❤ by GitHub

So this is all you need to search the text of procedures, views, triggers etc and the names of all tables and columns for a specific string. It works and you can use it now, BUT, there are major improvements to be made...next post will cover converting it to a stored procedure and using parameters, as well as better filtering of the output.

Here is the final version, with the temporary table and all other changes added:

	DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000)
	-- use temp table to store results
	IF EXISTS (SELECT OBJECT_ID('temp_db.dbo.##search_results')) DROP TABLE ##search_results
	CREATE TABLE ##search_results
	(
	database_nm varchar(256),
	Procedure_nm varchar(256),
	Table_nm varchar(256),
	Column_nm varchar(256),
	Job_nm varchar(256),
	Step_id varchar(256),
	Step_nm varchar(256),
	command_text varchar(256)
	)
	SELECT @search_str = 'tablename'
	DECLARE curDB
	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	GROUP BY name HAVING HAS_DBACCESS(name) = 1
	OPEN curDB
	FETCH NEXT FROM curDB INTO @db_nm
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	--SELECT @db_nm --removed, redundant
	-- add code here to insert results into new temp table
	SELECT @sql_str = 'INSERT INTO ##search_results(database_nm, Procedure_nm) SELECT distinct db_nm='''+@db_nm+''', OBJECT_NAME=o.name FROM '+@db_nm+'..syscomments s (NOLOCK)
	JOIN '+@db_nm+'..sysobjects o (NOLOCK) ON s.id = o.id
	WHERE text LIKE ''%'+@search_str+'%''
	INSERT INTO ##search_results(database_nm, Table_nm, Column_nm) SELECT TABLE_CATALOG as ''DATABASE'', TABLE_NAME, COLUMN_NAME
	FROM '+@db_nm+'.INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE ''%'+@search_str+'%'' or TABLE_NAME LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	FETCH NEXT FROM curDB INTO @db_nm
	END
	CLOSE curDB
	DEALLOCATE curDB
	SELECT @sql_str = 'INSERT INTO ##search_results(Job_nm, step_id, step_nm, command_text) SELECT j.name, s.step_id, s.step_name, s.command
	FROM MSDB..sysjobsteps s JOIN MSDB..sysjobs j ON s.job_id = j.job_id
	WHERE s.command LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	SELECT *
	FROM ##search_results

view raw String_reference.sql hosted with ❤ by GitHub

PART 2 - Improvements