Hi Mark,


Here is the code. I never tried this before as I didn't need it till now.
But as I have seen ur query I thought I should do this.

See the code
Just copy and paste and see whether it is running.

Make sure you write
use 'database name'
at the start and also specify the search string you need in the code.

here it is............

declare @tablenames varchar(200)
declare @columnames varchar(500)

drop table b#
select col.name 'columnname',obj.name 'tablename',obj.id into b#
from sysobjects obj
inner join syscolumns col on obj.id=col.id where obj.xtype='U'
select * from b#
declare @lstrquery1 nvarchar(4000)
declare @lstrquery2 nvarchar(4000)
declare @tname varchar(50)
declare @cname varchar(50)
declare @searchstring varchar(10)
set @searchstring='cha'
set @lstrquery1=''
set @lstrquery2=''
---declaration of 1st cursor
declare cur1 scroll cursor for
select distinct tablename from b# where tablename<>'b#'
open cur1

fetch first from cur1 into @tname
while @@fetch_status=0
begin

set @lstrquery1= @lstrquery1 + ' select * from ' + @tname + ' where '
--declaration of 2nd cursor
set @lstrquery2=''
declare cur2 scroll cursor for
select columnname from b# where tablename=@tname
open cur2

fetch first from cur2 into @cname
while @@fetch_status=0
begin

if @lstrquery2=''
set @lstrquery2=@lstrquery2 + @cname + ' like ' + char(39) + '%' + @searchstring + '%' + char(39)
else
set @lstrquery2=@lstrquery2 + ' or ' + @cname + ' like ' + char(39) + '%' + @searchstring + '%' + char(39)

fetch next from cur2 into @cname
end

close cur2
deallocate cur2
--end of 2nd cursor

set @lstrquery1 = @lstrquery1 + @lstrquery2
fetch next from cur1 into @tname
end
close cur1
deallocate cur1
select @lstrquery1
exec sp_executesql @lstrquery1