--下面这个存储过程用来在某个数据库中的所有表中查找某一字符串
----使用: 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