SQL Script to search for a string

String_reference.sql

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