Searching across your SQL Server Part 2

Click here for the first part of this post

As a continuation of my previous post on searching your SQL Server databases, below is a new and more efficient version of the search script. It allows you to customize the output in the SELECT statement from the results table before you run it to save extra querying after the results are created. The cursor has been eliminated in favor of a WHILE loop, and this could easily be converted to a stored procedure if you wanted to have it live on your server and be called by an application or other users. Of course there are probably small improvements that could be made, maybe I will explore some in the future, but you came here to get back to work and this script works.

	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

view raw SQL_search_string.sql hosted with ❤ by GitHub

Continue reading Part 3 - Converting this script into a stored procedure