列出Sybase的表結構,並注明主鍵順序

列出Sybase的表結構,並注明主鍵順序

declare @objname varchar(92)
declare @len1 int, @len2 int, @len3 int, @len4 int, @len5 int, @sysstat2 int,@indid int
declare @sqltext varchar(1024)
select @objname='TableName'
select Column_name = isnull(c.name, 'NULL'),
        Col_order = colid,
        Type = isnull(convert(char(30), x.xtname),
    isnull(convert(char(30),
     get_xtypename(c.xtype, c.xdbid)),
    t.name)),
        Length = c.length,
        Prec = c.prec,
        Scale = c.scale,
        Nulls = convert(bit, (c.status & 8)),
        Default_name = object_name(c.cdefault),
        Rule_name = object_name(c.domain),
        Access_Rule_name = object_name(c.accessrule),
        rtype = t.type, utype = t.usertype, xtype = c.xtype,
        Ident = convert(bit, (c.status & 0x80)),
        Object_storage =                       
   case when (c.xstatus is null) then NULL
                             when (c.xstatus & 1) = 1 then "off row"
                             else                          "in row " end,
  Prim_key=0
 into #helptype
 from syscolumns c, systypes t, sysxtypes x
  where c.id = object_id(@objname)
   and c.usertype *= t.usertype
   and c.xtype *= x.xtid
 update #helptype
  set Type = substring(Type, 1, 29) + "+"
  where xtype is not null
        and substring(Type, 29, 1) != " "
 /* Handle National Characters */
 update #helptype
  set Length = Length / @@ncharsize
  where (rtype = 47 and utype = 24)
     or (rtype = 39 and utype = 25)
 /* Handle unichar/univarchar */
 update #helptype
  set Length = Length / @@unicharsize
  where rtype in (select type from systypes
   where name in ('unichar', 'univarchar'))
 select @len1 = max(datalength(Column_name)),
        @len2 = max(datalength(Type)),
        @len3 = max(datalength(Default_name)),
        @len4 = max(datalength(Rule_name))
 from #helptype
select @indid=indid from sysindexes where id = object_id(@objname) and (status2 & 2 = 2 and status & 2048 = 2048)
if(@indid is not null)
begin
declare @i int,@thiskey varchar(30)
select  @i = 1
set nocount on
while @i <= 31
begin
select @thiskey=index_col(@objname,@indid , @i)
if(@thiskey is not null)
begin
update #helptype set where
select @i=@i+1
end
else
break
end
end

if (@len1 > 15 or @len2 > 15 or @len3 > 15 or @len4 > 15 )
     select @sqltext = 'select Column_name,
  Type, Length, Prec, Scale, Nulls, Default_name, Rule_name,
  Access_Rule_name, "Identity" = Ident,Prim_key'
 else
     select @sqltext = '
  select Column_name = convert(char(15), Column_name),
  Type = convert(char(15), Type), Length, Prec, Scale, Nulls,
  Default_name = convert(char(15), Default_name),
  Rule_name = convert(char(15), Rule_name),
  Access_Rule_name,
  "Identity" = Ident,Prim_key'
 /* Display the Object_storage only if there are object columns. */
 if exists (select * from #helptype
     where Object_storage is not null)
 begin
  select @sqltext = @sqltext + ", Object_storage "
 end
 execute (@sqltext + " from #helptype order by Col_order asc")
 drop table #helptype

--select * from sysindexes where id = object_id('Goods') and status&512=512