Sybase 查詢表結構方法詳解：替代 Oracle DESC 的實用技巧

背景痛點

Sybase ASE 無內建 DESC table_name 命令。sp_help 雖可查看結構，但輸出多個結果集，難以在腳本中程式化處理。

系統表查詢法（核心方案）

通過聯接系統表 syscolumns、systypes、sysxtypes 直接獲取欄位定義：

SELECT 
  ISNULL(c.name, 'NULL') AS "字段名",
  t.name AS "字段類型",
  c.length AS "字段長度",
  c.prec AS "精度",
  c.scale AS "小數位",
  CONVERT(BIT, (c.status & 8)) AS "允許NULL",
  OBJECT_NAME(c.cdefault) AS "默認值",
  OBJECT_NAME(c.domain) AS "規則"
FROM syscolumns c
JOIN systypes t ON c.usertype *= t.usertype
LEFT JOIN sysxtypes x ON c.xtype *= x.xtid
WHERE c.id = OBJECT_ID('目標表名')
ORDER BY c.colid;

關鍵系統表說明

狀態位解碼

• status & 8：是否允許 NULL（0=NOT NULL, 8=NULL）
• status & 0x80：是否身份列
status2 & 0x80加密列 status2 & 0x1000解密默認值 status2 & 0x40000LOB 列存儲方式

sp_help 源碼分析與簡化版

sp_help 存儲過程位於 sybsystemprocs 數據庫。核心邏輯在 IF (@sysstat & 15) IN (1,2,3) 分支（約第 556 行），構建臨時表 #helptype 後格式化輸出。

精簡版存儲過程封裝

CREATE PROCEDURE sp_desc_table @tabname VARCHAR(255)
AS
BEGIN
  SELECT 
    ISNULL(c.name, 'NULL') AS column_name,
    t.name AS data_type,
    c.length AS length,
    c.prec AS precision,
    c.scale AS scale,
    CASE WHEN (c.status & 8) = 8 THEN 1 ELSE 0 END AS nullable,
    OBJECT_NAME(c.cdefault) AS default_name,
    OBJECT_NAME(c.domain) AS rule_name
  FROM syscolumns c
  JOIN systypes t ON c.usertype = t.usertype
  WHERE c.id = OBJECT_ID(@tabname)
  ORDER BY c.colid;
END

常用對比：Oracle vs Sybase 表結構查詢

進階：獲取完整 DDL 腳本

Sybase 提供 ddlgen 工具（命令行）或 sp_ddl 系統過程（需單獨安裝）可生成建表腳本。

-- 命令行導出結構
ddlgen -Usa -Ppassword -Sserver -Ddbname -Ttable_name -N -Ooutput.sql