Thursday, March 10, 2011

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: , , , , , , , , ,

1 Comments:

At March 29, 2011 at 12:24 PM , Blogger AngelDeLaNoche said...

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