Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Search a value in character column of all tables

Dec 14 2009 2:22PM by Madhivanan   

Sometimes, you may need to find a value in the characters columns (char, varchar, etc) in all 
the tables

This is one of the methods to do it
declare @sql varchar(max),@search varchar(100)

set @sql=''

set @search='your search string'

select 
        @sql=@sql+'select distinct ''['+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''%'+@search+'%'' 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) 
The resultset would have table_name, column_name and column_value 

Tags: t-sql, sql_server, search,


Madhivanan
3 · 40% · 12899
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

5  Comments  

  • Hi,

    Can you please explain the method in detail and what does each variable contain and how the method works.

    Thank You.

    commented on Dec 15 2011 9:47AM
    Mevlish Dcosta
    3050 · 0% · 2
  • Superb man, i tried and found the results. good job..

    Keep helping..

    Thanks Abhinav

    commented on Aug 28 2012 4:44AM
    abhinavranjan19
    3050 · 0% · 2
  • Mevlish Dcosta, just print @sql and see what it generates

    commented on Aug 28 2012 4:52AM
    Madhivanan
    3 · 40% · 12899
  • Thanks Abhinav :)

    commented on Aug 28 2012 4:53AM
    Madhivanan
    3 · 40% · 12899
  • HI Madhivan,

    Gud Article

    Thanks a lot.

    commented on Jan 16 2013 5:06AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Search a value in character column of all tables " rated 5 out of 5 by 3 readers
Search a value in character column of all tables , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]