一些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>

评 论

论坛新帖

顶部 底部


建议使用IE 6.0以上浏览器,800×600以上分辨率,法律顾问:上海瀛东律师事务所 张楠律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2020, 沪ICP备05003035号
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪公网安备 31010102002173号

51Testing官方微信

51Testing官方微博

扫一扫 测试知识全知道