General Search Feature
Is there a way to search all fields in a table for a string value without
specifying each field by name? The reason I ask is that I have a database
of a few hundred tables each with unique fields, and I have a request to
develop a general search feature over everything in the database. The system
has been in production for 4 years, so there's no possibility of designing
this need into the system's core.
I don't think the brute force method (specifying every field in every table
- over a thousand fields) is going to be maintainable. Even having to specify
every table is borderline non-maintainable, but I am willing to live with
that.
Any ideas?
[716 byte] By [
Dave] at [2007-11-9 21:11:14]
# 1 Re: General Search Feature
if you are trying to setup this feature for users, you should look at
Full-text indexing at
http://www.sqlteam.com/item.asp?ItemID=114
http://www.google.com/search?as_q=sql+server+full-text+index&num=20&hl=en&ie
=UTF-8&oe=UTF-8&newwindow=1&btnG=Google+Search&as_epq=&as_oq=&as_eq=&lr=&as_
ft=i&as_filetype=&as_qdr=all&as_occt=any&as_dt=i&as_sitesearch=&safe=images
for a utility that does this for developers look at: SQL Server Find 2.2
http://www.webattack.com/get/sqlfind.shtml
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
"Dave" <dave_doknjas@yahoo.ca> wrote in message
news:3e80a113$1@tnews.web.dev-archive.com...
>
> Is there a way to search all fields in a table for a string value without
> specifying each field by name? The reason I ask is that I have a database
> of a few hundred tables each with unique fields, and I have a request to
> develop a general search feature over everything in the database. The
system
> has been in production for 4 years, so there's no possibility of designing
> this need into the system's core.
>
> I don't think the brute force method (specifying every field in every
table
> - over a thousand fields) is going to be maintainable. Even having to
specify
> every table is borderline non-maintainable, but I am willing to live with
> that.
>
> Any ideas?
>
# 2 Re: General Search Feature
I am assuming you are using SQL Server.
If you want to write something yourself, you can try to build a dynamic query
in stored procedure by using the INFORMATION_SCHEMA views to get all text
based (char, nchar, varchar, nvarchar, etc.) fields for a specified table.
You can run this stored procedure against each table in your database by
using the system stored procedure sp_MSForEachTable 'exec sproc_SearchText
?'
I hope this helps.
- Jason Rein
"Dave" <dave_doknjas@yahoo.ca> wrote:
>
>Is there a way to search all fields in a table for a string value without
>specifying each field by name? The reason I ask is that I have a database
>of a few hundred tables each with unique fields, and I have a request to
>develop a general search feature over everything in the database. The system
>has been in production for 4 years, so there's no possibility of designing
>this need into the system's core.
>
>I don't think the brute force method (specifying every field in every table
>- over a thousand fields) is going to be maintainable. Even having to specify
>every table is borderline non-maintainable, but I am willing to live with
>that.
>
>Any ideas?
>
