SQL语句，在数据库的所有表⾥查某⼀个字段值CREATE 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
增加字段的sql语句
close tbroy
deallocate tbroy
end
go
⽐如查的“⾸页列表”⽅法：
exec Full_Search '⾸页列表'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。