全库查询的sql
我想对一个数据库中所有表的所有字段进行查询，这样的语句怎么写？
[解决办法]
hiyun()你好，我已经更新了我的存储过程，对于表和字段为数据库关键字的处理了下，在我本机的sql 2005中多次调试通过了，你试一试吧！
Create procedure Full_Search(@string varchar(100))
as
begin
declare @tbname varchar(100)
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(100)
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(4000),@j int
select @sql= 'select @i=count(1) from ' +quotename(@tbname) + ' where '+ quotename(@colname)+ ' like '+ ' ' '% '+@string+ '% ' ' '
exec sp_executesql @sql,N '@i int output ',@i=@j output --输出满足条件表的记录数
if @j> 0
begin
declare @v varchar(8000)
set @v= 'select distinct '+quotename(@colname)+ ' from ' +quotename(@tbname) + ' where '+ quotename(@colname)+ ' like '+ ' ' '% '+@string+ '% ' ' '
exec(@v)
end
fetch next from colroy into @colname
end
close colroy
deallocate colroy
fetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
end
exec Full_Search 'Guy '
drop procedure Full_Search
