SQL search script final version

SQL_search_string.sql

DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000), @counter int

-- 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),
	Object_nm varchar(256),
	object_type varchar(256),
	Column_nm varchar(256),
)
IF EXISTS (SELECT object_id from tempdb.sys.all_objects where name like '#sql_string%') DROP TABLE #sql_string
CREATE TABLE #sql_string
(
sequence int, 
dbname varchar(256),
sqlstring varchar(500)
)

SELECT @search_str = 'id'
INSERT INTO #sql_string	
SELECT ROW_NUMBER() OVER (ORDER BY name) as 'sequence', name,
			dyn_sql = 'SELECT db_nm='''+name+''', OBJECT_NAME=o.name, o.xtype, null
			FROM '+name+'..syscomments s (NOLOCK)
			JOIN '+name+'..sysobjects o (NOLOCK) 
				ON s.id = o.id
			WHERE text LIKE ''%'+@search_str+'%''

			SELECT db_nm='''+name+''', C.TABLE_NAME,T.TABLE_TYPE, C.COLUMN_NAME
				FROM '+name+'.INFORMATION_SCHEMA.COLUMNS C
					JOIN '+name+'.INFORMATION_SCHEMA.TABLES T
					ON C.TABLE_NAME = T.TABLE_NAME
				WHERE C.COLUMN_NAME LIKE ''%'+@search_str+'%'' or C.TABLE_NAME LIKE ''%'+@search_str+'%'''
			FROM master.sys.databases
			GROUP BY name 
			HAVING HAS_DBACCESS(name) = 1

SELECT @counter = max(sequence) from #sql_string

WHILE @counter >0 
	BEGIN
	SELECT @sql_str = sqlstring from #sql_string where sequence = @counter
	INSERT INTO #search_results EXEC (@sql_str)
	SET @counter = @counter - 1
	CONTINUE
	END

SELECT *
	FROM #search_results

	DROP TABLE #sql_string