SQL Server Search Procedure SQLWorks.blogspot.com

SQL Server Search Procedure.sql

/*
Created by SQLWorks Consulting 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/

CREATE PROCEDURE usp_Search @search_str varchar(1000)
AS
SET NOCOUNT ON

DECLARE @db_nm varchar(256), @sql_str varchar(1000)

-- use temp table to store results
IF EXISTS (SELECT object_id  
           FROM   tempdb.sys.all_objects 
           WHERE  name LIKE '##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)
)
						

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

	-- added exclusion to procedure text search to eliminate views from appearing here as well
	-- seperated column and table name searches
	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+'%'' and o.type <> ''V'' 

					   INSERT INTO ##search_results(database_nm, Table_nm, Column_nm) SELECT TABLE_CATALOG as ''DATABASE'', TABLE_NAME, NULL
					   FROM '+@db_nm+'.INFORMATION_SCHEMA.TABLES
					   WHERE TABLE_NAME 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+'%'''
					   
	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)

-- modified this select to use proper names for results columns
SELECT database_nm as 'DataBase', 
		Procedure_nm as 'Procedure',
		Table_nm as 'Table_or_View',
		Column_nm as 'Column',
		Job_nm as 'Job',
		Step_id as 'Job_Step_ID',
		Step_nm as 'Job_Step_Name',
		command_text as 'Job_Step_Command'
	FROM ##search_results