Home > SQL, SQL 2005, SQL 2008 > SQL Search For String In Database

SQL Search For String In Database

I use following query to generate a set of “SELECT” statement which can later be executed to search for a given string in the database

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. You can further enhance this query to search for any type other than string.

Advertisements
Categories: SQL, SQL 2005, SQL 2008
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: