转换
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/
下面存储过程利用游标 以及以上的几个方法 搜索一个数据库内的所有表的固定类型的字段。
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
游标
1 alter procedure p_SlideAvg
2 (
3 @CityStr nvarchar(400),--城市字符串
4 @StationID nvarchar(10),--站点编号
5 @Type nvarchar(10),--类型
6 @sDate datetime, --开始时间
7 @eDate datetime --结束时间
8 )
9 --set @CityStr='06'
10 --set @Type='O3'
11 --set @sDate='2008-03-20'
12 --set @eDate='2008-04-20'
13 as
14 declare @CityID nvarchar(10) --城市ID
15 --declare @StationID nvarchar(10)--站点ID
16 declare @channel_num nvarchar(10)--类型的channel_num
17
18 --先删除数据
19 declare @sqlD nvarchar(1000)
20 set @sqlD='delete from TAB_SlideAvg where stationID='''+@StationID+''' and Date_Time>='''+convert(varchar,@sDate,120)+''' and date_time<='''+convert(varchar,@eDate,120)+''''
21 exec sp_executesql @sqlD
22
23 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,
24 tab_channel_baseinfo as c where a.id=b.group_id and b.id=c.station_id
25 and upper(c.channel_name)=upper(@Type)
26 and b.id=@StationID
27 and c.channel_state = 1
28 open C_SlideAvg --打开游标
29 fetch next from C_SlideAvg into @CityID,@StationID,@channel_num
30 while(@@fetch_status=0)
31 begin
32 ------------------------------------------------------------------------------------------------------------------------------
33
34 declare @days int --间隔的天数
35 declare @i int
36 set @i=0
37 set @days=datediff(day,@sDate,@eDate)
38 declare @mDate datetime
39 --开始循环日期相加
40 while @i<@days
41 begin
42 set @i=@i+1
43 set @mDate=DATEADD(day,@i,@sDate) --循环的每一天的变量 print convert(varchar,@mDate,120)
44 declare @h int
45 set @h=0
46 while @h<24--循环24小时
47 begin
48 declare @h24 datetime
49 --set @h24=DATEADD(Hour,@h,@mDate)
50 set @h24=dateadd(second,59,dateadd(minute,59,dateadd(hour,@h,@mDate)))--时间添加上@h个小时 59分59秒
51 declare @oldH datetime
52 set @oldH=DATEADD(Hour,-7,@h24) --当前小时数 减去7小时
53 declare @sql1 nvarchar(1000)
54 declare @slideAvg real
55 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)+''''
56 exec sp_executesql @sql1,N'@slideAvg real output',@slideAvg output
57 IF @slideAvg IS NOT NULL AND @slideAvg<>''
58 begin
59 declare @sql2 nvarchar(1000)
60 set @sql2='insert into Tab_Slideavg values('''+@CityID+''','''+@StationID+''','''+@channel_num+''','''+convert(varchar,@h24,120)+''','''+convert(varchar,@slideAvg,120)+''')'
61 exec sp_executesql @sql2
62 end
63 set @h=@h+1 --时间每次循环+1
64 end
65 end
66
67 -------------------------------------------------------------------------------------------------------------------------------
68 fetch next from C_SlideAvg into @CityID,@StationID,@channel_num --取下一条记录
69 end
70 close C_SlideAvg--关闭游标
71 deallocate C_SlideAvg--销毁游标
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
