SQL search for a string in a database
Generate a set of SELECT statements that search every text column in the database for a given string.
1 min read#SQL
When you know a value exists somewhere in a database but not which table holds it, this query generates one SELECT per text column. Copy the output and run it.
sql
select
'select distinct ''' + tab.name + '.' + col.name + ''' from
[' + tab.name + '] where [' + col.name + '] like ''%TEXT_TO_SEARCH%'' 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');
Replace TEXT_TO_SEARCH with the term you are looking for. Each generated line ends with union, so remove the last one before running the batch.
You can extend the types.name filter to search columns of other types as well.