asked on

sql server search query

place search term in 'SEARCH_TERM_WE_ARE_LOOKING_FOR'

this query will find a search term in a database of type varchar or int

but it does not find
[orderid] [int] IDENTITY(1,1) NOT NULL,

declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), @data varchar(50)
set @data = 'SEARCH_TERM_WE_ARE_LOOKING_FOR'
declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, systypes c where a.id = b.id and a.type = 'U' and c.xtype = b.xtype and c.name in ( 'varchar', 

'nvarchar', 'text', 'ntext' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
begin
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data + '%'')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + @col + '], * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data 

+ '%''
                        '
      exec(@sql)
 
      fetch next from cur_tbl into @tbl, @col
end
close cur_tbl
deallocate cur_tbl

Open in new window

Bill Prew

If the column you are trying to search actually contains the string you show:

[orderid] [int] IDENTITY(1,1) NOT NULL,

then the brackets will confuse the LIKE clause. You can work around this by specifying an ESCAPE character, take a look at the following:

http://msdn.microsoft.com/en-us/library/ms179859.aspx

~bp

ASKER CERTIFIED SOLUTION

lof

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

and you are sure this text is exactly written like the string you concatinate?

Take the proof before the
exec(@sql)
add a statement to let you show your sql
print @sql

Now you see what is executed, copy that and paste it to execute it yourself ..... correct it until it works

rgb192

ASKER

thanks, now i can find int