下面这个存储过程用来在某个数据库中的所有表中查找某一字符串

下面这个存储过程用来在某个数据库中的所有表中查找某一字符串
----使用: EXEC Search '张三'

CREATE PROCEDURE Search @Str varchar(100),@SearchFlag  int=1,@TableFlag int=1
/**
**@Str 要搜索的字符串
**@TableFlag 1: 只在用户表中查找；2：只在系统表中查找;其他:在所有表中查找
**@SearchFlag 1: 精确查询；其他:模糊查询
**/
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