TSql – Search all columns and all tables for text

Posted: 2012-12-07 in Sql
Tags: ,

Create Sql queries that search all tables and all columns for a specific item. Very handy for quick “where is it stored” investigations

Change @searchString to what you need (must be sql delimited) then run the query.
This will create sql queries which will help you find what you table and column it resided in.


-- chocosmith.wordpress.com

DECLARE @searchString varchar(250)
 set @searchString = 'search for this'

SELECT 'select ''' + TABLE_NAME + ':' + COLUMN_NAME +''' as ''Table-Column'' , * from ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' where ' + COLUMN_NAME + ' like ''%'+            @searchString + '%'''
 FROM INFORMATION_SCHEMA.COLUMNS
 where DATA_TYPE in('nchar', 'nvarchar', 'varchar', 'char')

It is extremely processor intensive to run the scripts so I excluded an automated EXEC, instead just copy and paste the queries into management studio.

Please note I restricted it to just basic text types, you can easily expand on data_type if you wish (like text).

Advertisements

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