The following stored procedure is used to search for a string in all tables in a database.

The following stored procedure is used to search for a string in all tables in a database.
---- Use: exec search 'zhangsan'

Create procedure search @ STR varchar (100), @ searchflag Int = 1, @ tableflag Int = 1
/**
** @ STR: string to be searched
** @ Tableflag 1: search only in the User table; 2: search only in the system table; others: Search in all tables
** @ Searchflag 1: exact query; others: fuzzy query
**/
As
Begin
Create Table # tablelist (tablename sysname, colname sysname)
Declare @ table sysname
Declare @ Col sysname
Set nocount on
If @ tableflag = 1
Declare curtab scroll cursor for select name from sysobjects where xtype = 'U' and status> 0
Else
If @ tableflag = 2
Declare curtab scroll cursor for select name from sysobjects where xtype = 'S'
Else
Declare curtab scroll cursor for select name from sysobjects where xtype = 's' or xtype = 'U'
Open curtab
Fetch next from curtab into @ table
While @ fetch_status = 0
Begin
Declare curcol scroll cursor for select name from syscolumns where (xtype = 175 or xtype = 167 or xtype = 239 or xtype = 231) and (ID in (select ID from sysobjects where name = @ table ))
Open curcol
Fetch next from curcol into @ col
While @ fetch_status = 0
Begin
If @ searchflag = 1
Execute ('insert into # tablelist select ''' + @ table + ''', ''' + @ Col + ''' from '+ @ table + 'where' + @ Col +' = ''' + @ STR + '''')
Else
Execute ('insert into # tablelist select ''' + @ table + ''', ''' + @ Col + ''' from '+ @ table + 'where' + @ Col + 'like ''' +' % '+ @ STR +' % '+ '''')
Fetch next from curcol into @ col
End
Close curcol
Deallocate curcol
Fetch next from curtab into @ table
End
Close curtab
Deallocate curtab
Set nocount off
Select distinct * from # tablelist
Drop table # tablelist
End
Go