整理了一些t-sql技巧

标题   整理了一些t-sql技巧      选择自 kingwkb 的 Blog 关键字   整理了一些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 ' ) 七、 分区视图 分区视图是提高查询性能的一个很好的办法 -- 看下面的示例 -- 示例表 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           aa_更新 11          cc_更新 21          ee_更新 （所影响的行数为 3 行） ==*/ 八、 树型的实现 -- 参考 -- 树形数据查询示例 -- 作者: 邹建 -- 示例数据 create   table   [ tb ] ( [ id ]   int   identity ( 1 , 1 ), [ pid ]   int ,name  varchar ( 20 )) insert   [ tb ]   select   0 , ' 中国 ' union    all    select   0 , ' 美国 ' union    all    select   0 , ' 加拿大 ' union    all    select   1 , ' 北京 ' union    all    select   1 , ' 上海 ' union    all    select   1 , ' 江苏 ' union    all    select   6 , ' 苏州 ' union    all    select   7 , ' 常熟 ' union    all    select   6 , ' 南京 ' union    all    select   6 , ' 无锡 ' union    all    select   2 , ' 纽约 ' union    all    select   2 , ' 旧金山 ' go -- 查询指定id的所有子 create   function  f_cid( @id   int ) returns   @re   table ( [ id ]   int , [ level ]   int ) as begin   declare   @l   int   set   @l = 0   insert   @re   select   @id , @l   while   @@rowcount > 0   begin    set   @l = @l + 1    insert   @re   select  a. [ id ] , @l    from   [ tb ]  a, @re  b    where  a. [ pid ] = b. [ id ]   and  b. [ level ] = @l - 1   end /**/ /**/ /**/ /*--如果只显示最明细的子(下面没有子),则加上这个删除  delete a from @re a  where exists(   select 1 from [tb] where [pid]=a.[id]) --*/   return end go -- 调用(查询所有的子) select  a. * ,层次 = b. [ level ]   from   [ tb ]  a,f_cid( 2 )b  where  a. [ id ] = b. [ id ] go -- 删除测试 drop   table   [ tb ] drop   function  f_cid go   九、 排序问题 CREATE   TABLE   [ t ]  (   [ id ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,   [ GUID ]   [ uniqueidentifier ]   NULL   )  ON   [ PRIMARY ] GO 下面这句执行5次 insert  t  values  ( newid ()) 查看执行结果 select   *   from  t 1、 第一种 select   *   from  t   order   by   case  id  when   4   then   1                    when   5   then   2                    when   1   then   3                    when   2   then   4                    when   3   then   5   end 2、 第二种 select   *   from  t  order   by  (id + 2 ) % 6 3、 第三种 select   *   from  t  order   by   charindex ( cast (id  as   varchar ), ' 45123 ' ) 4、 第四种 select   *   from  t WHERE  id  between   0   and   5 order   by   charindex ( cast (id  as   varchar ), ' 45123 ' ) 5、 第五种 select   *   from  t  order   by   case   when  id  > 3   then  id - 5   else  id  end 6、 第六种 select   *   from  t  order   by  id  /   4   desc ,id  asc   十、 一条语句删除一批记录 首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删 除了，比循环用多条语句高效吧应该。 delete   from   [ fujian ]   where   charindex ( ' , ' + cast ( [ id ]   as   varchar ) + ' , ' , ' , ' + ' 5,6,8,9,10,11, ' + ' , ' ) > 0 还有一种就是 delete   from  table1  where  id  in ( 1 , 2 , 3 , 4  ) 十一、获取子表内的一列数据的组合字符串 下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。 CREATE     FUNCTION  fn_Get05LvshiNameBySuo  ( @p_suo   Nvarchar ( 50 )) RETURNS   Nvarchar ( 2000 ) AS BEGIN      DECLARE   @LvshiNames   varchar ( 2000 ),  @name   varchar ( 50 )   select   @LvshiNames = ''   DECLARE  lvshi_cursor  CURSOR   FOR 数据库里有1,2,3,4,5 共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?