一些t-sql技巧 | 郑州计算机学校_郑州电脑学校_郑州电脑培训

24

07-2014

一些t-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′)

该日志 2014年07月24日发表在网络安全技术分类下, 通告目前不可用,你可以至底部留下评论。
转载请注明: 一些t-sql技巧 | 郑州计算机学校_郑州电脑学校_郑州电脑培训