Search an Entire Database
How often have you needed to search an entire database for a particular piece of information? Common problem, many different solutions are available on the internet. In my case I needed something that I could run from an intranet web site, written in classic ASP, that searched an in-house SQL 2005 database. Here is the code I finally came up with that will search all fields of all tables, with a selection to choose which database. Set the four values in the configuration section and edit the script timeout variable as needed.
<html><head><title>Database Search</title></head><body>
<%
'-------------------------------
'Set these values:
sqladdress = "ServerName"
sqluser = "Username" 'Usually the SA account
sqlpass = "Password"
filename = "SearchDB.asp" 'Name you saved this file as
Server.ScriptTimeout = 90 'Maximum number of seconds that a script can run before it is terminated
'-------------------------------
if Session("SearchDB") <> "True" then Response.Redirect "default.asp"
On Error resume Next
DBName = request.querystring ("DBName")
SearchText = trim(request.querystring ("SearchText")&" ")
NewSearch = request.querystring ("NewSearch")
exact = request.querystring("exact")
hide = request.querystring("hide")
if len(DBName) > 0 and len(SearchText) > 0 and NewSearch <> "Y" then
response.write "<p>Searching <b>" & DBName & "</b> for <b>" & SearchText & "</b>"
if exact = "Y" then response.write " using exact match"
response.write ".</p>"
response.write "<p><a href=""" & filename & "?NewSearch=Y&DBName=" & DBName & "&SearchText=" & SearchText & "&exact=" & exact & """>New Search</a></p>"
SearchText = replace (SearchText, "'", "''")
sqlopen = "Provider=SQLOLEDB;Data Source=" & sqladdress & ";"
sqlopen = sqlopen & "Initial Catalog=" & DBName & ";User Id=" & sqluser & ";Password=" & sqlpass & ";"
sqlopen = sqlopen & "Connect Timeout=15;Network Library=dbmssocn;"
Set dbcon = Server.CreateObject("ADODB.Connection")
dbcon.Open sqlopen
Set dbcon2 = Server.CreateObject("ADODB.Connection")
dbcon2.Open sqlopen
Set dbcon3 = Server.CreateObject("ADODB.Connection")
dbcon3.Open sqlopen
Set rs = dbcon.Execute("SELECT * from sys.tables ORDER BY name")
while not rs.eof
TableName = rs.fields("Name")
if TableName <> "dtproperties" then
response.write "<p>Table - <b>" & TableName & "</b><br />"
Set rs2 = dbcon2.Execute("SELECT * from " & TableName & ";")
for each f in rs2.Fields
FieldName = f.Name
if exact = "Y" then
strSQL = "SELECT * from " & TableName & " WHERE " & FieldName & " = '" & SearchText & "';"
else
strSQL = "SELECT * from " & TableName & " WHERE " & FieldName & " LIKE '%" & SearchText & "%';"
end if
Set rs3 = dbcon3.Execute(strSQL) 'text search
If Err.number <> 0 then
if err.number <> -2147217913 and err.number <> 424 and err.number <> 3704 and err.number <> -2147217900 then
txtMsg = "Warning:<br /> " & Err.Source & " | " & Err.Number & " | " & Err.description & "<br /> " & strSQL & "<br />"
else
'These errors will only occur when Exact Match is selected.
'424 Object required
'3704 Operation is not allowed when the object is closed.
'-2147217900 The data types ntext and varchar are incompatible in the equal to operator.
'-2147217913 [Various type conversion errors.]
if hide <> "Y" then txtMsg = "Note: " & Err.Source & " | " & Err.Number & " | " & Err.description & "<br />"
end if
err.clear
if isnumeric(SearchText) then
strSQL = "SELECT * from " & TableName & " WHERE " & FieldName & " = " & SearchText & ";"
Set rs3 = dbcon3.Execute(strSQL) ' numeric search
If Err.number <> 0 then
response.write "Warning:<br /> " & Err.Source & " | " & Err.Number & " | " & Err.description & "<br /> " & strSQL & "<br />"
err.clear
else
while not rs3.eof
response.write FieldName & " - " & rs3.fields(FieldName) & "<br />"
rs3.movenext
wend
end if
else
response.write txtMsg
end if
else
while not rs3.eof
response.write FieldName & " - " & rs3.fields(FieldName) & "<br />"
rs3.movenext
wend
end if
rs3.close
next
response.write "</p>"
end if
rs.Movenext
wend
dbcon3.Close
Set dbcon3 = Nothing
dbcon2.Close
Set dbcon2 = Nothing
dbcon.Close
Set dbcon = Nothing
response.write "<p>Search Complete.</p>"
else
sqlopen = "Provider=SQLOLEDB;Data Source=" & sqladdress & ";"
sqlopen = sqlopen & "Initial Catalog=;User Id=" & sqluser & ";Password=" & sqlpass & ";"
sqlopen = sqlopen & "Connect Timeout=15;Network Library=dbmssocn;"
Set dbcon = Server.CreateObject("ADODB.Connection")
dbcon.Open sqlopen
%>
<form action="<%=filename%>" method="get">
<p>DB to search <select name="DBName" id="DBName"><option value=""></option><%
Set rs = dbcon.Execute("SELECT name FROM sys.databases WHERE state=0 and name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY name;")
while not rs.eof
tmpName = rs.fields("Name")
response.write "<option value=""" & tmpName & """"
if tmpName = DBName then response.write " SELECTED"
response.write ">" & tmpName & "</option>"
rs.Movenext
wend
%></select></p>
<p>Text to find <input name="SearchText" type="text" size="40" value = "<%=SearchText%>" /></p>
<p>Exact match <input type="checkbox" name="exact" value="Y"<%if exact = "Y" then response.write " checked"%>></p>
<p>Hide Errors <input type="checkbox" name="hide" value="Y" checked></p>
<p><input name="submit" type="submit" value="Begin Search" /></p>
</form>
<%
dbcon.Close
Set dbcon = Nothing
end if
%>
</body></html>Labels: asp, columns, database, db, fields, find, search, sql, tables, vba
posted by AngelDeLaNoche @ 12:44 PM
1 Comments
1 Comments:
I updated the code and added an option for exact match. Also in the code above is an undocumented option to control access using a session variable that I set in a separate login page.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home