Mssql reverse Query

Author: oldjun & emperor
Unfortunately, I got a database with hundreds of tables and N fields in many tables. I didn't want to manually detect useful tables and fields one by one and wrote some SQL statements, with the help of oldjun, we have this script. The function is to use a keyword as the search condition, traverse the entire database, and display all the locations of the keyword in the database, that is, the table name and column.

 

Directly give the code:

<Title> SQL reverse query </title>
<Form name = "Form" action = "? ">
<Input type = "text" name = "keyword" value = "Enter the keyword to search">
<Input type = "Submit" value = "Submit">
</Form>
<%
Search a keyword from each column in each table of a database
By oldjun (http://www.oldjun.com)
Based on huangzi (http://www.2chuizi.com) s SQL
Respect for the authors work

Server. ScriptTimeout = 999999999
Response. Buffer = true
On Error Resume Next
Keyword = request ("keyword ")
If keyword = "" then
Response. write "Need keyword! "
Response. End
End if
Dim conn
Set conn = Server. CreateObject ("ADODB. Connection ")
Dim ConnStr
ConnectionString, Pls change!
ConnStr = "Driver = {SQL SERVER}; Server = localhost; UID = bbs; PWD = bbs123456789; Database = syc163"
Conn. open ConnStr
Conn.exe cute ("drop table huangzi_table ")
Conn.exe cute ("create table huangzi_table (id int identity (1, 1), biaoid int, biaoname nvarchar (1000 ))")
Conn.exe cute ("insert huangzi_table select [id], [name] from sysobjects where xtype = U ")

Set rs limit conn.exe cute ("select count (id) as tnum from huangzi_table ")
Tnum = rs ("tnum ")
Rs. close
Set rs = nothing
For I = 1 to tnum

Set rsbiao into conn.exe cute ("select biaoid from huangzi_table where id =" & I &"")
Biaoid = rsbiao ("biaoid ")
Set rst into conn.exe cute ("select [biaoname] from huangzi_table where biaoid =" & biaoid &"")
Tname = rst ("biaoname ")

Set rsl=conn.exe cute ("select count ([name]) as lnum from syscolumns where id =" & biaoid &"")
Lnum = rsl ("lnum ")

For j = 1 to lnum

Topnum = J-1
Set rslieappsconn.exe cute ("select top 1 [name] from syscolumns where id =" & biaoid & "and [name] not in (select top" & topnum & "[name] from syscolumns where id = "& biaoid &") ")
Liename = rslie ("name ")
Set rsresult=conn.exe cute ("select top 1 [" & liename & "] from [" & tname & "] where CAST ([" & liename & "] as nvarchar (1000 )) = "& keyword &"")

If rsresult. bof or rsresult. eof then
Response. write "Nothing-" & tname & ":" & liename
Response. write "<br>"
Else
Result = rsresult (liename)
Response. write "table name:" & tname & "column name:" & liename & "keyword [" & keyword & "]"
Response. write "<br>"
End if

Rslie. close
Set rslie = nothing
Rsresult. close
Set rsresult = nothing
Next

Rsbiao. close
Set rsbiao = nothing
Rst. close
Set rst = nothing
Rsl. close
Set rsl = nothing
Next

Conn.exe cute ("drop table huangzi_table ")
%>