一些 T-SQL 技巧_MsSql_

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

    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')

    七、 分区视图

    分区视图是提高查询性能的一个很好的办法

    --看下面的示例

    --示例表
    create table tempdb.dbo.t_10(
    id int primary key check(id between 1 and 10),name varchar(10))

    create table pubs.dbo.t_20(
    id int primary key check(id between 11 and 20),name varchar(10))

    create table northwind.dbo.t_30(
    id int primary key check(id between 21 and 30),name varchar(10))
    go

    --分区视图
    create view v_t
    as
    select * from tempdb.dbo.t_10
    union all
    select * from pubs.dbo.t_20
    union all
    select * from northwind.dbo.t_30
    go

    --插入数据
    insert v_t select 1 ,'aa'
    union  all select 2 ,'bb'
    union  all select 11,'cc'
    union  all select 12,'dd'
    union  all select 21,'ee'
    union  all select 22,'ff'

    --更新数据
    update v_t set name=name+'_更新' where right(id,1)=1

    --删除测试
    delete from v_t where right(id,1)=2

    --显示结果
    select * from v_t
    go

    --删除测试
    drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
    drop view v_t

    /**//*--测试结果

    id          name
    ----------- ----------
    1           

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！