Sybase数据库技术，数据库恢复专家

在oracle的sqlplus中有一个命令：desc，能够简要的显示基表、视图等的结构。desc的显示结果简单明了，比较实用。虽然在Sybase ASE中的存储过程sp_help也能实现查看表结构的目的；但是，显示结果很多尤其换行混乱，让人看得眼花缭乱。显示的存储过程：sp_desc能够实现oracle的sqlplus中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的语法请 ：下载