SQL: search for a string in every varchar column in a database

Question

I have a database where a misspelled string appears in various places in different tables. Is there a SQL query that I can use to search for this string in every possible varchar/text column in the database?

I was thinking of trying to use the information_schema views somehow to create dynamic queries, but I'm not sure if that will work, or if there's a better way.

I'm using MS SQL Server if that helps.

Answer 1

Using the technique found here the following script generates SELECT's for all ((n)var)char columns in the given database. Copy/paste the output, remove the very last 'union' and execute.. You'll need to replace MISSPELLING HERE with the string you're looking for.

select 
'select distinct ''' + tab.name + '.' + col.name 
+ '''  from [' + tab.name 
+ '] where [' + col.name + '] like ''%MISSPELLING HERE%'' union ' 
from sys.tables tab 
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id) 
where tab.type_desc ='USER_TABLE' 
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');