Finding Something Somewhere in a Database

I threw this ugly query together (at the bottom of the message) a few years ago (hence the ancient system table names) and recently stumbled across it in an old email I sent to myself. I figured I would post it here, since it’s a handy way to find a string in a database when you have no idea where to begin looking.

I wrote it to find a string within a database I was unfamiliar with. You provide a search term to the script -- @searchString -- such as ‘%acre%’ (use the standard LIKE wildcards) in the example below, and it searches all of the char, varchar, nchar, and nvarchar columns in all of the user tables for your term. For my initial needs, all of the tables were in the dbo schema, so the script doesn’t handle table owner/schema. You can tweak it to your needs on that front.

The result set gives you your initial search term, how many instances where found in which table, and the query used to generate the count for that table so you have a good starting place for further analysis.

DECLARE @searchString nvarchar(100)

SET @searchString = '%acre%'

SET NOCOUNT ON

CREATE TABLE ##search_results (result_table sysname, search_term NVARCHAR(100), result_count bigint)

CREATE TABLE ##search_queries (result_table sysname, query_text NVARCHAR(MAX))

DECLARE @tbl sysname

set @tbl = (select top 1 so.name FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

order by so.name desc )

declare @sql NVARCHAR(max), @where nvarchar(max)

while @tbl is not null

BEGIN

set @where = ''

SELECT @where = @where + ' OR ' + quotename(sc.name) + ' LIKE ''' + @searchString + '''' FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

and so.name = @tbl

-- get rid of the initial ' OR '

SET @where = substring(@where, 4, len(@where))

SET @sql = 'SELECT ' + quotename(@tbl, '''') + ' AS resultsTable, ' + quotename(@searchString, '''') + ' AS searchTerm, count(*) AS resultsCount FROM ' + quotename(@tbl) + ' WHERE '

INSERT ##search_queries (result_table, query_text) VALUES (@tbl, @sql + @where)

SET @sql = 'INSERT ##search_results ' + @sql + @where

EXEC sp_executesql @statement = @sql

set @tbl = (select top 1 so.name FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

AND so.name < @tbl

order by so.name desc)

END

SELECT sr.search_term, sr.result_count, sr.result_table, sq.query_text FROM ##search_results AS sr JOIN ##search_queries AS sq ON sr.result_table = sq.result_table ORDER BY sr.result_count DESC, sr.result_table DESC

DROP TABLE ##search_results

DROP TABLE ##search_queries