sql server 常用函数 【游标】

转换

CAST([字段名] as varchar(4000)）
CONVERT(varchar(4000),[字段名]）

1.查询数据库内所有的表

--查询数据库内的 用户的 表，u表示用户表(就是你自己创建的表)，s表示系统表
select * from sysobjects where xtype='u'

2.查询数据库内所有表内的所有字段

--查询表内的所有的列 name:表字段名称 所属表的id xtype:字段的类型用id表示的
select * from syscolumns

name:字段名称

id：所属于表的系统id

xtype：就是字段的类型ID

可以通过select * from systypes 查询到所属的类型对应的 是int还是varchar！

获取一个表内的所有字段，是系统的函数，object_id('表名') 获取到的是表在系统内的id

select * from syscolumns where id=object_id('部门收文')

 and xtype in (select xtype from systypes where name in ('varchar','nvarchar','char','nchar'))--并且字段的类型你个是 nvarchar,varchar char的

3.查询系统内支持所有字段的类型

select * from systypes

4.函数quotename('表名')

当表的名字是sql server中的关键字时，需要在将表明 放在尖括号中如： ['index'] index就是一个关键字。
可以通过函数quotename来解决。

print('select * from '+quotename('sys_log'))

输出：select * from [sys_log]

quotename用法可参考：

http://blog.163.com/zangyunling@126/blog/static/164624505201132110142270/

sql server 游标参考：http://www.cnblogs.com/wudiwushen/archive/2010/03/30/1700925.html

 

下面存储过程利用游标 以及以上的几个方法 搜索一个数据库内的所有表的固定类型的字段。

alter procedure Full_Search(@string varchar(100))
as
begin
declare @tbname varchar(100) 
declare tbroy cursor  for select name from sysobjects  
where xtype='u' and (name like 'T0%' or name like 'T2%')  --第一个游标遍历以T0,T2开头的所有表

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 * from ' +quotename(@tbname) +'  where '+ quotename(@colname)+' like '+'''%'+@string+'%'''  ---输入满足条件表的信息
 --set @v='select  distinct  ' +quotename(@colname)+' from ' +quotename(@tbname) +'  where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
 exec(@v)
 --print @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

 

 

---------------------用游标 循环 更新Gis记录
declare @id numeric(18, 0)
declare @i int
set @i=0
declare updateGis cursor for select C0058_ID from T0058_PointObjectGis where c0058_table='C0130_EnerBaseInfo'
open updateGis --打开游标
fetch next from updateGis into @id
while(@@fetch_status=0)
begin
update T0058_PointObjectGis set c0058_table='T0130_EnerBaseInfo' where C0058_ID=@id
set @i=@i+1
--取下一条记录
fetch next from updateGis into @id 
end
close updateGis
deallocate updateGis --删除游标
print('更新记录数------------>'+convert(varchar(50),@i))

 游标实例，循环表内数据，并根据参数 查询其他表内数据，拼接SQL 插入另一个表

 

declare @CityStr nvarchar(400)
declare @Type nvarchar(10)
declare @sDate datetime --开始时间
declare @eDate datetime    --结束时间
declare @CityID nvarchar(10)
declare @StationID nvarchar(10)
declare @channel_num nvarchar(10)
set @CityStr='06'
set @Type='O3'
set @sDate='2008-03-01'
set @eDate='2013-01-20'
declare C_SlideAvg cursor for select a.id as CityID,b.id as StationId,c.channel_num from TAB_BASE_GROUP as a,TAB_STATION_BASEINFO as b,
tab_channel_baseinfo as c where a.id=b.group_id and a.id in(@CityStr) and b.id=c.station_id  and upper(c.channel_name)=upper(@Type) 
and c.channel_state = 1
open C_SlideAvg --打开游标
fetch next from C_SlideAvg into @CityID,@StationID,@channel_num
while(@@fetch_status=0)
begin
------------------------------------------------------------------------------------------------------------------------------

declare @days int --间隔的天数
declare @i int
set @i=0
set @days=datediff(day,@sDate,@eDate)
declare @mDate datetime
--开始循环日期相加
while @i<@days
begin
    set @i=@i+1
    set @mDate=DATEADD(day,@i,@sDate) --循环的每一天的变量
    declare @h int
    set @h=0
    while @h<24--循环24小时
    begin
        declare @h24 datetime
        set @h=@h+1 --时间每次循环+1
        set @h24=DATEADD(Hour,@h,@mDate)
        declare @oldH datetime
        set @oldH=DATEADD(Hour,-7,@h24) --当前小时数 减去8小时
        declare @sql1 nvarchar(1000)
        --set @sql1='select avg(val) as SlideAvg from S'+@StationID+'HN where channel_num='''+@channel_num+''' and date_time>='+convert(varchar,@oldH,120)+' and date_time<='''+convert(varchar,@h24,120)+''''
        declare @slideAvg real
        set @sql1='select @slideAvg=avg(val) from S'+@StationID+'HN where channel_num='''+@channel_num+''' and date_time>='''+convert(nvarchar,@oldH,120)+''' and date_time<='''+convert(nvarchar,@h24,120)+''''
        --print @sql1
        exec sp_executesql @sql1,N'@slideAvg real output',@slideAvg output
        IF @slideAvg IS NOT NULL AND @slideAvg<>''
        begin
            declare @sql2 nvarchar(1000)
            set @sql2='insert into Tab_Slideavg values(''06'','''+@StationID+''','''+@channel_num+''','''+convert(varchar,@h24,120)+''','''+convert(varchar,@slideAvg,120)+''')'
            exec sp_executesql @sql2
        end
    end
end

-------------------------------------------------------------------------------------------------------------------------------
fetch next from C_SlideAvg into @CityID,@StationID,@channel_num --取下一条记录
end
close C_SlideAvg--关闭游标
deallocate C_SlideAvg

 

alter procedure p_SlideAvg
(
@CityStr nvarchar(400),--城市字符串
@StationID nvarchar(10),--站点编号
@Type nvarchar(10),--类型
@sDate datetime, --开始时间
@eDate datetime    --结束时间
)
--set @CityStr='06'
--set @Type='O3'
--set @sDate='2008-03-20'
--set @eDate='2008-04-20'
as
declare @CityID nvarchar(10) --城市ID
--declare @StationID nvarchar(10)--站点ID
declare @channel_num nvarchar(10)--类型的channel_num

--先删除数据
declare @sqlD nvarchar(1000)
set @sqlD='delete from TAB_SlideAvg where stationID='''+@StationID+''' and Date_Time>='''+convert(varchar,@sDate,120)+''' and date_time<='''+convert(varchar,@eDate,120)+''''
exec sp_executesql @sqlD

declare C_SlideAvg cursor for select a.id as CityID,b.id as StationId,c.channel_num from TAB_BASE_GROUP as a,TAB_STATION_BASEINFO as b,
tab_channel_baseinfo as c where a.id=b.group_id and b.id=c.station_id  
and upper(c.channel_name)=upper(@Type) 
and b.id=@StationID
and c.channel_state = 1
open C_SlideAvg --打开游标
fetch next from C_SlideAvg into @CityID,@StationID,@channel_num
while(@@fetch_status=0)
begin
------------------------------------------------------------------------------------------------------------------------------

declare @days int --间隔的天数
declare @i int
set @i=0
set @days=datediff(day,@sDate,@eDate)
declare @mDate datetime
--开始循环日期相加
while @i<@days
begin
    set @i=@i+1
    set @mDate=DATEADD(day,@i,@sDate) --循环的每一天的变量     print convert(varchar,@mDate,120)
    declare @h int
    set @h=0
    while @h<24--循环24小时
    begin
        declare @h24 datetime
        --set @h24=DATEADD(Hour,@h,@mDate)
        set @h24=dateadd(second,59,dateadd(minute,59,dateadd(hour,@h,@mDate)))--时间添加上@h个小时 59分59秒 
        declare @oldH datetime
        set @oldH=DATEADD(Hour,-7,@h24) --当前小时数 减去7小时
        declare @sql1 nvarchar(1000)
        declare @slideAvg real
        set @sql1='select @slideAvg=avg(val) from S'+@StationID+'HN where channel_num='''+@channel_num+''' and date_time>='''+convert(nvarchar,@oldH,120)+''' and date_time<='''+convert(nvarchar,@h24,120)+''''
        exec sp_executesql @sql1,N'@slideAvg real output',@slideAvg output
        IF @slideAvg IS NOT NULL AND @slideAvg<>''
        begin
            declare @sql2 nvarchar(1000)
            set @sql2='insert into Tab_Slideavg values('''+@CityID+''','''+@StationID+''','''+@channel_num+''','''+convert(varchar,@h24,120)+''','''+convert(varchar,@slideAvg,120)+''')'
            exec sp_executesql @sql2
        end
        set @h=@h+1 --时间每次循环+1
    end
end

-------------------------------------------------------------------------------------------------------------------------------
fetch next from C_SlideAvg into @CityID,@StationID,@channel_num --取下一条记录
end
close C_SlideAvg--关闭游标
deallocate C_SlideAvg--销毁游标