Sybase ASE中实现类似oracle的sqlplus中desc命令来简要显示表结构的存储过程

在oracle的sqlplus中有一个命令:desc,能够简要的显示基表、视图等的结构。desc的显示结果简单明了,比较实用。虽然在Sybase ASE中的存储过程sp_help也能实现查看表结构的目的;但是,显示结果很多尤其换行混乱,让人看得眼花缭乱。显示的存储过程:sp_desc能够实现oracle的sqlplus中desc相同的功能,仅仅多了一列:列序号。

效果图如下:

实现类似oracle的sqlplus中desc想类似功能的sp_desc存储过程

 

存储过程sp_desc的语法如下:

------=================================================================================

use sybsystemprocs
go

if exists(select 1 from sybsystemprocs.dbo.sysobjects where type="P" and name="sp_desc")
  drop proc sp_desc
go

create procedure sp_desc
    @tablename varchar(128)
as
    declare @table_id int
    declare @char_bin_types   varchar(30)
    set nocount on
begin    
    select @char_bin_types = char(47)+char(39)+char(45)+char(37)+char(35)+char(34)
    select @table_id = object_id(@tablename)
   
    select
    "序号"=convert(varchar(3),colid),
    "列名"=convert(varchar(30),column_name),
    "是否为空?"=convert(varchar(7),nullable),
    "类型"=convert(varchar(30),
          (case scale
                      when NULL then
                          case
                          when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
                          else
                              type_name+"("+convert(varchar,precision)+")"
                          end
                         
                      when 0 then
                          case
                          when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
                          else
                              type_name+"("+convert(varchar,precision)+","+convert(varchar,scale)+")"
                          end
                      else
                          case 
                          when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
                          else
                              type_name+"("+convert(varchar,precision)+","+convert(varchar,scale)+")"
                          end
                      end
           )
      )
    from
    (   
    SELECT    /* INTn, FLOATn, DATETIMEn and MONEYn types */
            colid = c.colid,
            column_name = c.name,
            nullable =    /* set nullability from status flag */
                (case convert(smallint, convert(bit, c.status&8)) when 0 then "NOT NULL" else "" end),                   
            type_name = rtrim(substring(isnull(stuff(d.type_name,
                    (c.status&128)/128,
                    char_length(d.type_name),
                    "numeric identity"), d.type_name),
                    1+isnull(d.aux,
                    ascii(substring("III<<<MMMI<<A<A",
                    2*(d.ss_dtype%35+1)+2-8/c.length,
                    1))-60), 18)),
            "precision" = isnull(convert(int, c.prec),
                         isnull(convert(int, d.data_precision),
                        convert(int,c.length)))
                    +isnull(d.aux, convert(int,
                    ascii(substring("???AAAFFFCKFOLS",
                    2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
        scale = isnull(convert(smallint, c.scale),
                       convert(smallint, d.numeric_scale))
                    +convert(smallint,
                    isnull(d.aux,
                    ascii(substring("<<<<<<<<<<<<<<?",
                    2*(d.ss_dtype%35+1)+2-8/c.length,
                    1))-60))
        FROM
            syscolumns c,
            sysobjects o,
            sybsystemprocs.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @table_id
            AND c.id = o.id
            AND c.usertype = t.usertype
            AND t.type = d.ss_dtype
            AND d.ss_dtype IN (111, 109, 38, 110, 43)    /* Just *N types */
            AND c.usertype < 100        /* No user defined types */
        UNION
        SELECT    /* All other types including user data types */
            colid = c.colid,
            column_name = c.name,
            nullable =    /* set nullability from status flag */
                (case convert(smallint, convert(bit, c.status&8)) when 0 then "NOT NULL" else "" end),
            type_name = rtrim(substring(isnull(stuff(d.type_name,
                    (c.status&128)/128,
                    char_length(d.type_name),
                    "numeric identity"), d.type_name),
                    1+isnull(d.aux,
                    ascii(substring("III<<<MMMI<<A<A",
                    2*(d.ss_dtype%35+1)+2-8/c.length,
                    1))-60), 18)),
            "precision" = isnull(convert(int, c.prec),
                     isnull(convert(int, d.data_precision),
                    convert(int,c.length)))
                    +isnull(d.aux, convert(int,
                    ascii(substring("???AAAFFFCKFOLS",
                    2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
            scale = isnull(convert(smallint, c.scale),
                       convert(smallint, d.numeric_scale)) +
                    convert(smallint, isnull(d.aux,
                    ascii(substring("<<<<<<<<<<<<<<?",
                    2*(d.ss_dtype%35+1)+2-8/c.length,
                    1))-60))
        FROM
            syscolumns c,
            sysobjects o,
            sybsystemprocs.dbo.spt_datatype_info d,
            systypes t
        WHERE
            o.id = @table_id
            AND c.id = o.id
            AND c.usertype = t.usertype
            AND t.type = d.ss_dtype
            AND (d.ss_dtype NOT IN (111, 109, 38, 110, 43) /* No *N types */
                OR c.usertype >= 100) /* User defined types */
    ) a
    ORDER BY colid
end
go

------=================================================================================
 

存储过程sp_desc的语法请 :下载

  • 本文链接地址:http://www.dbainfo.net/sybase-ase-sameas-sqlplus-desc-sp_desc.htm
  • 本文为dbainfo个人原创,请在尊重作者劳动成果的前提下进行转载;
  • 转载务必注明原始出处 : Sybase数据库技术,数据库恢复专家
  • 对《Sybase ASE中实现类似oracle的sqlplus中desc命令来简要显示表结构的存储过程》有何疑问或见解,请在本文下方发表;
  • 对网站还有其他问题或建议,请提交在留言板,谢谢!
  • 目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)