How to search specific value in character column of all tables?

Few questions are asked in forums about searching a specific value across all tables in the database stored in character columns (char, varchar, etc)

There can be several methods to make this possible. Here is one of them

Let us create this stored procedure

create procedure search_all_tables 
(
	@seach_string varchar(max)
)
as
declare @sql varchar(max)
set @sql=''

select 
   @sql=@sql+'select '''+c.table_name+''' as table_name,
		'''+c.column_name+''' as column_name,
		['+c.column_name+'] as column_value 
from '+c.table_name+' 
where 
	['+c.column_name+'] like N''%'+@seach_string+'%''
	 union all ' 
from 
        information_schema.columns as c 
	inner join information_schema.tables as t 
	on c.table_name=t.table_name 
where 
        t.table_type='BASE TABLE' and 
        data_type like '%char%' 
set @sql=left(@sql,len(@sql)-10)
exec(@sql) 

Also create few tables

create table test1 (col1 int, col2 varchar(100))
insert into test1 
select 1,'welcome to sql server'
GO
create table test2 (col1 int, col2 varchar(max))
insert into test2 
select 10,'welcome to sql server'
GO
create table test3 
(
col1 int, 
col2 varchar(max),
col3 varchar(200)
)
insert into test3
select 10,'welcome to database programming','sql version 2012'
union all
select 11,'welcome',''

GO
create table test4 (col1 int, col2 varchar(max))
insert into test4
select 22,'Unix shell programming'

Suppose you want to find out the table name, column name and string value that contains the value ‘sql’, you can execute the procedure as shown below

EXECUTE search_all_tables 'sql'

The result is

table_name column_name column_value
---------- ----------- -------------------------
test1      col2        welcome to sql server
test2      col2        welcome to sql server
test3      col3        sql version 2012

It listed out all the column names where string value sql is part of it.

Suppose you want to find out the table name, column name and string value that contains the value ‘programming’, execute the following

EXECUTE search_all_tables 'programming'

The result is

table_name column_name column_value
---------- ----------- --------------------------------
test4      col2        Unix shell programming
test3      col2        welcome to database programming

This way you can make use of this stored procedure to search for a character values stored in character columns of any user defined tables in the entire database.

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