Hello,
I am trying to implement a fulltext search on a MS SQL database. While I can query each table independently for a specific word, I didn't find a method to query the entire collection of tables for my search key. Is there a way to do this (using the fulltext catalog for example)?
Thanks!
Mark
pub00515 0 Newbie Poster
chmonalisa 0 Light Poster
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
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.