You can create SQL statements directly:
CREATE TABLE qresults (tname nvarchar (370), CNAME nvarchar (3630), [count] int) declare @tname nvarchar ($) DECLARE @cname n varchar (declare) @countOut nvarchar ($) Declare @sql nvarchar (max) DECLARE c_search cursor Forselect t.name,c.name From sysobjects t inner join syscolumns C on t.id=c.id where t.type= ' U ' and C.xtype in (56,167,175,231,239) Order by T.nam E--xtype This place limits the type int varchar char nvarchar nchar Five types open c_searchfetch next from C_search into @tname, @cnamewhile @ @FET Ch_status=0begin set @sql =n ' Select @countx =count (*) from ' [email protected] + ' where [' [email protected] + '] = ' 44 ' ' Print @sql EXECUTE sp_executesql @sql, N ' @countx nvarchar ($) out ', @[email protected] out insert into qresults VALUES (@tname, @cname, @countOUT) fetch next from C_search to @tname, @cnameendselect tname ' Table ', cname ' Field ' fro M qresults where [count]>0 order by Tnameclose c_searchdeallocate c_searchdrop table Qresults
Or create a stored procedure first:
ALTER proc Full_search (@string varchar) AS BEGIN DECLARE @tbname varchar () Declare Tbroy cursor FOR SELECT Name from sysobjects where xtype= ' u '--the first cursor iterates through all tables open Tbroy fetch next from Tbroy to @tbname while @ @fetch_s Tatus=0 BEGIN DECLARE @colname varchar (DECLARE) Colroy cursor for select name from syscolumns where id=object_ ID (@tbname) and Xtype in (the Select Xtype from systypes where name in (' varchar ', ' nvarchar ', ' char ', ' nchar ')--data A field of type character)--the second cursor is a nested cursor of the first cursor, traversing all fields of a table open Colroy fetch NEXT from Colroy to @colname while @ @fetch_status =0 Begin declare @sql nvarchar (+), @j int Select @sql = ' Select @i=count (1) from ' [email protected] + ' where ' + @colname + ' like ' + ' '% ' [email protected]+ '% ' exec sp_executesql @sql, N ' @i int output ', @[email protecte D] Output-the number of records that satisfy the condition table if @j> 0 BEGIN Select contains the string's table name [email protected]--exec (' SELECT distinct ' [email  ;p rotected]+ ' from ' [emAil protected] + ' where ' + @colname + ' like ' + '% ' [email protected]+ '% '] END fetch NEXT from Colroy int o @colname End Close Colroy deallocate Colroy fetch NEXT from Tbroy to @tbname end close Tbroy deallocat E Tbroy End
Then execute, enter the specified value.
SQL-queries all tables that contain the specified values