general search page inquiry

[gcapp]

I was wondering if anyone knew of a site or a script which will search a web site that has multiple databases. My site has around 15 databases and I wanted to have a search page that would search all.

Or if anyone has another suggestion on how to go about this, I would appreciate it.

Thanks,
Gary

[whammy]

Something like that would usually need to be specific to your databases... if they are all on the same server, you can query your system tables (I forget which ones offhand) to start with...

systables? I forget... anyway in one of them they will have a type of 'U' and your table names under the field 'name'. If you query this table

WHERE type='U'

Then you should get all of the user tables, that should get you started, and you can build a custom search query from there...

[gcapp]

whammy,
I appreciate the help - I am relatively new to asp, so are there any places where I could start what you were talking about?? Any sites or anything that you know of where I could dig deeper into this subject??

My databases are all ont he same server by the way.

Thanks

[whammy]

Hmm, not really - you could build this like any other search/query page - I assume you're using SQL Server? If so then go into enterprise manager and have a look at your system tables (don't alter them in any way though!!!).

If you haven't written any database searches, I'd try doing a simple one first using a form where someone can select fields, i.e.


FieldName SearchType SearchText
__________ __________ __________
|Fieldname1| |Equals | |__________|
|Fieldname2| |Contains |
|__________| |__________|


And then building a SQL Query from the information entered, i.e.


If Request.Form("SearchText") <> "" Then
MyQuery = "SELECT * FROM TABLENAME WHERE " & Request.Form("FieldName")

Select Case Request.Form("SearchType")
Case "Equals"
MyQuery = MyQuery & " = '" & Request.Form("SearchText") & "'"
Case "Contains"
MyQuery = MyQuery * " LIKE '%" & Request.Form("SearchText") & "%'"
End Select

'Response.Write(MyQuery) : Response.End ' for testing
Set rs = Conn.Execute(MyQuery)
Do While NOT rs.EOF
' results here
rs.MoveNext
Loop
Else
'Response.Write("You didn't enter anything to search for!")
End If


That's a basic kind of specific search anyway, if you just want a plain old search box that will search all of your databases, you would just have one field entered and you would need to specify yourself which databases/fields you wanted to search... wish I could help more...

You might be able to find something on SQL Server system tables on MSDN, or with a google search ?