Monday, August 31, 2009

Search all columns of all tables for a pattern

Today at work someone wanted to know how to search all columns of all tables for a pattern.  The code below uses a recursive select to generate dynamic SQL which will search all varchar columns for the specified pattern.  Not all columns are able to be cast to varchar.
declare @searchString varchar(max)
set @searchString = '%dealer sites%' --the pattern to search for

declare @queryToRun varchar(max) --internal variable.
--this variable needs to be non null otherwise it will make the recursive select return null.
set @queryToRun = ''

--create a statement to search all columns of all tables for the text.
SELECT @queryToRun = @queryToRun + 'select '''+t.table_name +'.'+c.column_name+''' from ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' where ' + QUOTENAME(c.column_name) + ' like '''+ @searchString + ''' union '
                  INFORMATION_SCHEMA.COLUMNS c on t.table_name = c.table_name
                  WHERE             TABLE_TYPE = 'BASE TABLE' and data_type='varchar'

--trim off the last union statement.
select @queryToRun = left(@queryToRun, datalength(@queryToRun) - datalength('union '))

--run the query.
exec (@queryToRun)

No comments: