SQL Server Multi-table search string

SQL Server Multi-table search string

We described a number of SQL Server articles earlier, today we mainly introduce the SQL Server under the condition of not knowing the table name of the search string, we all know that there will be more than one database under the time of the form, we are looking for the content does not know in which form, What about that? Today we are going to solve this problem by writing SQL statements;

We DB4 the database under the test to create two tables, respectively, insert some data, see the following table;

We tested an identical piece of data in both tables, searching for the string Zs in the Uname field;

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/07/4E/wKiom1nHV_CyGRuPAAEoFJdRwZM529.png-wh_500x0-wm_ 3-wmp_4-s_2551988444.png "title=" 1.png "width=" "height=" 395 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:600px; height:395px; "alt=" Wkiom1nhv_cygrupaaeofjdrwzm529.png-wh_50 "/>

The SQL statement we wrote;

declare  @string  varchar () set  @string  =  ' ZS '  --replaced with the string to find declare  @tabName  varchar (+), @colName  varchar (+) declare  @sql  varchar ($) declare  @tsql  varchar (8000) Declare tabcursor cursor forselect name from sysobjects where xtype  =  ' u '  AND name <>  ' dtproperties ' open tabcursorfetch next  from tabcursor into  @tabNameWHILE  @ @fetch_status  = 0BEGINset  @tsql  =   ' Declare colcursor cursor for select name from syscolumns where  id=object_id (@tabName)  and xtype=167OPEN colCursorFETCH NEXT from  colcursor into  @colNameWHILE  @ @fetch_status  = 0BEGINSET  @sql  =  ' if ( Exists (select * from  '  +  @tabName  +  '  where  ' set  @sql  =   @sql  +&nbsp, @colName  +  '  like  '% '  +  @string  +  '% '))  begin select  * from  ' set  @sql  =  @sql  +  @tabName  +  '  where  '  +  @colName  +  '  like  '% '  +  @string  +  '% ';select  ' +   @tabName  +  '  as tablename end ' set  @tsql  =  @tsql  + @ sql +  '; ' fetch next from colcursor into  @colNameENDexec (@tsql) close colcursordeallocate  colCursorFETCH NEXT from tabCursor INTO  @tabNameENDCLOSE   Tabcursordeallocate tabcursor

After execution, the execution results are displayed;

The search string displays column information and table information;

The structure ZS string is in the first column of the info table, and then through the first row in the Tb_info table

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/07/4E/wKiom1nHWAvQVpWuAAJZKZiKS0Y245.png-wh_500x0-wm_ 3-wmp_4-s_1559955324.png "title=" 2.png "width=" "height=" 526 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:800px; height:526px; "alt=" Wkiom1nhwavqvpwuaajzkziks0y245.png-wh_50 "/>

This article from "Gao Wenrong" blog, declined reprint!

SQL Server Multi-table search string