searchFor.sql

-- Description: Performs a case insensitive search against the column for similarly formatted
-- tables in mssql (since some closed box systems make it hard to determine which table contains a particular lookup item) 
-- replace ismv7 and table prefix with your own
DECLARE @searchFor varchar(50)
DECLARE @targetSchema varchar(50)
DECLARE @tableFormat varchar(50)

-- WHAT ARE YOU LOOKING FOR?
set @searchFor = 'fumigation'
set @tableFormat = 'TABLE_LOOKS_LIKE_THIS%'
set @targetSchema = 'YOURSCHEMA'

DECLARE @sqlCommand varchar(MAX)
DECLARE @tableName varchar(50)
declare @counter int
set @counter = 0
set @sqlCommand = 'select * from ('	
DECLARE c1 CURSOR READ_ONLY
FOR
select table_name FROM INFORMATION_SCHEMA.Tables where table_name like @tableFormat

OPEN c1

FETCH NEXT FROM c1
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
	set @counter = @counter + 1
	if( @counter > 1 )BEGIN
		set @sqlCommand = @sqlCommand + ' union '
	END

	set @sqlCommand = @sqlCommand + 'select ''' + @tableName + ''' as srcTable,descript from ' + @targetSchema + '.' + @tableName

	FETCH NEXT FROM c1
	INTO @tableName

END
-- Do the search of the unions!
exec(@sqlCommand + ') a where upper(a.descript) like upper(''' + '%' + @searchFor + '%' + ''')')
CLOSE c1
DEALLOCATE c1