SQL 遍历所有表

ALTER proc Full_Search(@string varchar(50))

as

begin

 

declare @tbname varchar(50)

declare tbroy cursor for select name from sysobjects where xtype= 'u ' --第一个游标遍历所有的表

 

open tbroy

fetch next from tbroy into @tbname while @@fetch_status=0

begin

declare @colname varchar(50)

declare colroy cursor for select name from syscolumns

where id=object_id(@tbname) and xtype in (

select xtype from systypes where name in ( 'varchar ', 'nvarchar ', 'char ', 'nchar ') --数据类型为字符型的字段

) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段

 

open colroy

fetch next from colroy into @colname while @@fetch_status=0

begin

 

declare @sql nvarchar(1000),@j int

select @sql= 'select @i=count(1) from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%'''

exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数

if @j> 0

BEGIN

select 包含字串的表名=@tbname

--exec( 'select distinct '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')

END

fetch next from colroy into @colname

end

close colroy

 

deallocate colroy

 

fetch next from tbroy into @tbname

end

close tbroy

deallocate tbroy

end

go

 

exec Full_Search '123'