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)
*/
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname --标记
end
fetch next from colroy into @colname
end
close colroy
deallocate colroy fetch next from tbroy into @tbname
end close tbroy
deallocate tbroy select * from @t
end 转来个贴子
问几个问题关于在while里声明的变量
只是第一次被编译吗?
其生存期是到while结束时吗?如:(标记)
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname
这个变量声明在while里面
然后
如果每次取出游标里的值后进去循环时,都执行declare的话,那岂不是和想要的结果不一致
如果不是
那么
那种在while里的变量声明和把声明写在最外面,功能应该是一致的吧讨论一下吧
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)
*/
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname --标记
end
fetch next from colroy into @colname
end
close colroy
deallocate colroy fetch next from tbroy into @tbname
end close tbroy
deallocate tbroy select * from @t
end 转来个贴子
问几个问题关于在while里声明的变量
只是第一次被编译吗?
其生存期是到while结束时吗?如:(标记)
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname
这个变量声明在while里面
然后
如果每次取出游标里的值后进去循环时,都执行declare的话,那岂不是和想要的结果不一致
如果不是
那么
那种在while里的变量声明和把声明写在最外面,功能应该是一致的吧讨论一下吧
只是第一次被编译吗?
其生存期是到while结束时吗?:生成执行计划得时候已经编译了,一执行语句,就分配空间。如:(标记)
declare @t table(Id int identity,ColValue varchar(200),ColName varchar(200),Tabname varchar(200))
insert into @t select @string,@colname,@tbname 这个变量声明在while里面
然后
如果每次取出游标里的值后进去循环时,都执行declare的话,那岂不是和想要的结果不一致:等于在一开始定义变量
如果不是
那么
那种在while里的变量声明和把声明写在最外面,功能应该是一致的吧:完全一致
declare @i int
set @i=1
while @i<10
begin
declare @ii int
select @ii=@i*@i,@i=@i+1
print @ii
endprint '@ii:' + rtrim(@ii)/*
1
4
9
16
25
36
49
64
81
@ii:81
*/
set @i = 1
while @i < 5
begin
declare @j int
print '@i='+ltrim(@i)+char(9)+'@j='+ltrim(isnull(@j,0))+char(9)+'@i+@j='+ltrim(isnull(@j,0)+@i)
set @j=@i+isnull(@j,0)
set @i=@i+1
end/*
@i=1 @j=0 @i+@j=1
@i=2 @j=1 @i+@j=3
@i=3 @j=3 @i+@j=6
@i=4 @j=6 @i+@j=10
*/
set @i=1
while @i<10
begin
declare @ii int
select @ii=isnull(@ii,0)+@i*@i,@i=@i+1
print rtrim(@i-1)+': '+rtrim(@ii)
endprint '@ii: ' + rtrim(@ii)/*
1: 1
2: 5 --如果@ii重新编译,这里应该输出4而不是5
3: 14
4: 30
5: 55
6: 91
7: 140
8: 204
9: 285
@ii: 285 --这里已经出了while控制,但@ii依然在
*/
if 1=2
begin
declare @i int
endset @i = 1
select @i
/*
zyz0304360
2008/05/10
traversal all databases for looking into the value
*/
alter procedure sp_traversal (@string nvarchar(800))
as
begin
declare @dbname nvarchar(800)
declare dbtraversal cursor for
select name from master..sysdatabases
open dbtraversal
fetch next from dbtraversal into @dbname
while @@fetch_status=0
begin
declare @tbname nvarchar(800)
exec('declare tbtraversal cursor for select name from '+@dbname+'..sysobjects
where xtype=''u''')
open tbtraversal
fetch next from tbtraversal into @tbname
while @@fetch_status=0
begin
declare @colname nvarchar(800)
exec('declare coltraversal cursor for select name from '+@dbname+'..syscolumns
where id=object_id('''+@dbname+'..'+@tbname+''') and xtype in (
select xtype from '+@dbname+'..systypes
where name in (''varchar'',''nvarchar'',''char'',''nchar'')--can be changed as other types
)')
open coltraversal
fetch next from coltraversal into @colname
while @@fetch_status=0
begin
declare @sql nvarchar(4000),@j int
select @sql='select @i=count(1) from '+@dbname+'..'+quotename(@tbname) +' where '+quotename(@colname)+' like '+'''%'+@string+'%''' --like
--select @sql='select @i=count(1) from '+@dbname+'..'+quotename(@tbname) +' where '+quotename(@colname)+' ='''+@string+'''' --equal
exec sp_executesql @sql,N'@i int output',@i=@j output
if @j>0
begin
declare @result table(ID int identity,[DataBase] nvarchar(800),Tabname nvarchar(800),ColName nvarchar(800),ColValue nvarchar(800))
insert into @result select @dbname,@tbname,@colname,@string
end
fetch next from coltraversal into @colname
end
close coltraversal
deallocate coltraversal
fetch next from tbtraversal into @tbname
end
close tbtraversal
deallocate tbtraversal
fetch next from dbtraversal into @dbname
end
close dbtraversal
deallocate dbtraversalselect * from @result
end 抄来的东西
改成了对整个服务器进行遍历
虽然功能的用处不大
但是,我学到了几个知识点
见笑哈哈,不错