Declare @findData varchar(100),
	@isDate bit,
	@isNumeric bit,
	@isInt bit,
	@isBit bit,
	@isDecimal bit

Set @findData = 'Search for this string!'
--Set @findData = '4561347' -- or this number
--Set @findData = '4/23/1980' -- or this Date


Set @isDate = isdate(@findData)
Set @isNumeric = isNumeric(@findData)
if (@isNumeric=1)
Begin
	Set @isInt = case when convert(decimal(18,9),@findData) - convert(int,convert(decimal(18,9),@findData)) = 0 then 1 else 0 end
	Set @IsDecimal = 1
End
Else
Begin
	Set @isInt = 0
	Set @IsDecimal = 0
End

Set @isBit = case when @findData in ('0','1','true','false') then 1 else 0 end


Declare @t table (
	sqlStr varchar(500)
)

Insert Into @t
Select 
	'Select tableName=''' + A.Name + ''', ColumnName=''' + B.Name + ''', Value=convert(varchar,'+ B.Name + '), Cnt=Count(*) From ' + A.Name + ' Where ' + B.Name + ' = convert(' + C.Name + ',''' + @findData + ''') Group By convert(varchar,'+ B.Name + ')'
	--,A.Name, C.Name, B.*
From sysobjects A
Inner Join syscolumns B
On A.id = B.id
Inner Join systypes C
On B.xType = C.xType
Where A.type = 'U'
and (
	(C.Name in ('int') and @isInt = 1)
	or
	(C.Name in ('decimal','float','money') and @isDecimal = 1)
	or
	(C.Name in ('datetime','smalldatetime') and @isDate = 1) 
	or
	(C.Name in ('bit') and @isBit = 1) 
	or
	C.Name in ('char','varchar','nvarchar','nchar')
	--or
	--C.Name not in ('int','datetime','bit','char','varchar','money','nchar','nvarchar','image','text','sysname','binary','varbinary','xml','smalldatetime','decimal')
)


--Select * From @t

Declare @results table (
	TableName varchar(100),
	ColumnName varchar(100),
	Value varchar(100),
	Cnt int
)

Declare @sqlStr varchar(500)

While exists(Select * From @t)
Begin
	Set @sqlStr = (Select top 1 sqlStr From @t)
	Delete From @t Where sqlStr = @sqlStr
	
	Insert Into @results
	Exec(@sqlStr)
End

Select * From @results

quote:

---

Originally posted by jimf

I come across this on a smaller scaler all the time, when you get the "can't convert ` to a numeric" type answer.
I use select * from INFORMATION_SCHEMA.COLUMNS WHERE< whatever>
to get all teh table names and column names and the cut and past that into excel and use excel to write all the
select * from <table> where col1 like '%`' or col2 like '%`%' etc. It's actually faster this way than writing and executing dynamic sql.

Jim

Everyday I learn something that somebody else already knew

---