一些t-sql技巧

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2008-7-22 16:08

字体：大中小 | 上一篇 | 下一篇 | 我要投稿

作者：未知来源：网络转载

test

SQL

　　一、只复制一个表结构，不复制数据

select top 0 * into [t1] from [t2]

　　二、获取数据库中某个对象的创建脚本

　　1、先用下面的脚本创建一个函数

　　if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
　　drop function fgetscript
　　go

　　create function fgetscript(
　　@servername varchar(50)    --服务器名
　 @userid varchar(50)='sa'    --用户名,如果为nt验证方式,则为空
　　@password varchar(50)=''    --密码
　　@databasename varchar(50)    --数据库名称
　　@objectname varchar(250)    --对象名

　　) returns varchar(8000)
　　as
　　begin
　　declare @re varchar(8000)        --返回脚本
　　declare @srvid int,@dbsid int      --定义服务器、数据库集id
　　declare @dbid int,@tbid int        --数据库、表id
　　declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量

　　--创建sqldmo对象
　　exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
　　if @err <>0 goto lberr

　　--连接服务器
　　if isnull(@userid,'')='' --如果是 Nt验证方式
　　begin
　　exec @err=sp_oasetproperty @srvid,'loginsecure',1
　　if @err <>0 goto lberr

　　exec @err=sp_oamethod @srvid,'connect',null,@servername
　　end
　　else
　　 exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password

　　if @err <>0 goto lberr

　　--获取数据库集
　　exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
　　if @err <>0 goto lberr

　　--获取要取得脚本的数据库id
　　exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
　　if @err <>0 goto lberr

　　--获取要取得脚本的对象id
　　exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
　　if @err <>0 goto lberr

　　--取得脚本
　　exec @err=sp_oamethod @tbid,'script',@re output
　　if @err <>0 goto lberr

　　--print @re
　　return(@re)

　　lberr:
　　exec sp_oageterrorinfo NULL, @src out, @desc out
　　declare @errb varbinary(4)
　　set @errb=cast(@err as varbinary(4))
　　exec master..xp_varbintohexstr @errb,@re out
　　set @re='错误号:'+@re
　　 +char(13)+'错误源:'+@src
　　+char(13)+'错误描述:'+@desc
　　return(@re)
　　end
　　go

　　2、用法如下

　　print dbo.fgetscript('服务器名','用户名','密码','数据库名','表名或其它对象名')

　　3、如果要获取库里所有对象的脚本，如如下方式

　　declare @name varchar(250)
　　declare #aa cursor for
　　select name from sysobjects where xtype not in('S','PK','D','X','L')
　　open #aa
　　fetch next from #aa into @name
　　while @@fetch_status=0
　　begin
　　print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
　　fetch next from #aa into @name
　　end
　　close #aa
　　deallocate #aa

　　4、声明，此函数是csdn邹建邹老大提供的

　　三、分隔字符串

　　如果有一个用逗号分割开的字符串，比如说"a,b,c,d,1,2,3,4"，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。

　　1、获取元素个数的函数

　　create function getstrarrlength (@str varchar(8000))
　　returns int
　　as
　　begin
　　declare @int_return int
　　declare @start int
　　declare @next int
　　declare @location int
　　select @str =','+ @str +','
　　select @str=replace(@str,',,',',')
　　select @start =1
　　select @next =1
　　select @location = charindex(',',@str,@start)
　　while (@location <>0)
　　begin
    　　select @start = @location +1
    　　select @location = charindex(',',@str,@start)
    　　select @next=@next+1
　　end
　　select @int_return = @next-2
　　return @int_return
　　end

　　2、获取指定索引的值的函数

　　create function getstrofindex (@str varchar(8000),@index int =0)
　　returns varchar(8000)
　　as
　　begin
　　 declare @str_return varchar(8000)
　　 declare @start int
　　 declare @next int
　　declare @location int
　　select @start =1
　　select @next =1 --如果习惯从0开始则select @next =0
　　select @location = charindex(',',@str,@start)
　　while (@location <>0 and @index > @next )
　　begin
    　　select @start = @location +1
    　　select @location = charindex(',',@str,@start)
    　　select @next=@next+1
　　end
　　if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后
　　select @str_return = substring(@str,@start,@location-@start)--@start肯定是逗号之后的位置或者就是初始值1
　　if (@index <> @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。
　　return @str_return
　　end

　　3、测试

　　SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
　　SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

　　四、一条语句执行跨越若干个数据库
　　我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
　　第一种方法:

　　select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名

　　第二种方法:
　　先使用联结服务器:

　　EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
　　exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
　　GO

　　然后你就可以如下：

　　select * from 别名.库名.dbo.表名
　　insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
　　select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
　　go

　　五、怎样获取一个表中所有的字段信息
　　蛙蛙推荐：怎样获取一个表中所有字段的信息
　　先创建一个视图

　　Create view fielddesc
　　as
　　select o.name as table_name,c.name as field_name,t.name as type,c.length as

　　length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
　　from syscolumns c
　　join systypes t on c.xtype = t.xusertype
　　join sysobjects o on o.id=c.id
　　left join sysproperties p on p.smallid=c.colid and p.id=o.id
　　where o.xtype='U'

　　查询时:

　　Select * from fielddesc where table_name = '你的表名'

　　还有个更强的语句，是邹建写的，也写出来吧

　　SELECT
　　(case when a.colorder=1 then d.name else '' end) N'表名',
　　a.colorder N'字段序号',
　　a.name N'字段名',
　　(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
　　(case when (SELECT count(*)
　　FROM sysobjects
　　WHERE (name in
          (SELECT name
          FROM sysindexes
          WHERE (id = a.id) AND (indid in
                    (SELECT indid
                  FROM sysindexkeys
                  WHERE (id = a.id) AND (colid in
                            (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
        (xtype = 'PK'))>0 then '√' else '' end) N'主键',
　　b.name N'类型',
　　a.length N'占用字节数',
　　COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
　　isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
　　(case when a.isnullable=1 then '√'else '' end) N'允许空',
　　isnull(e.text,'') N'默认值',
　　isnull(g.[value],'') AS N'字段说明'
　　--into ##tx

　　FROM syscolumns a left join systypes b
　　on a.xtype=b.xusertype
　　inner join sysobjects d
　　on a.id=d.id and d.xtype='U' and d.name <>'dtproperties'
　　left join syscomments e
　　on a.cdefault=e.id
　　left join sysproperties g
　　on a.id=g.id AND a.colid = g.smallid
　　order by object_name(a.id),a.colorder

　　六、时间格式转换问题

　　因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有"+"操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。

　　1、把所有"70.07.06"这样的值变成"1970-07-06"

　　UPDATE lvshi
　　SET shengri = '19' + REPLACE(shengri, '.', '-')
　　WHERE (zhiyezheng = '139770070153')

　　2、在"1970-07-06"里提取"70","07","06"

　　SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
SUBSTRING(shengri, 9, 2) AS day
　　FROM lvshi
　　WHERE (zhiyezheng = '139770070153')

　　3、把一个时间类型字段转换成"1970-07-06"

　　UPDATE lvshi
　　SET shenling = CONVERT(varchar(4), YEAR(shenling))
      + '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
      month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
      END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
      day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
　　WHERE (zhiyezheng = '139770070153')

21/212 >

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选