在数据库维护和开发过程中，我们经常需要导出数据库的表结构信息，包括表名、字段名、字段类型、长度以及注释等。对于南大通用数据库 GBASE-8S（基于 Informix），我们可以通过查询系统表来获取这些信息。

本文提供了一个经过整理和美化的 SQL 脚本，用于导出 GBASE-8S 数据库中的表结构信息。

SQL 脚本

该脚本关联了 systables（表信息）、syscolumns（列信息）、syscomments（表注释）和 syscolcomments（列注释）等系统表，并对字段类型进行了转换处理，使其更易读。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SELECT
    t.tabname AS 表名,
    c.colname AS 字段名,
    scc.comments AS 字段注释,
    -- ########## 开始：数据类型转换逻辑 ##########
    (CASE MOD(c.coltype, 256)
        WHEN 0 THEN 'CHAR'
        WHEN 1 THEN 'SMALLINT'
        WHEN 2 THEN 'INTEGER'
        WHEN 3 THEN 'FLOAT'
        WHEN 4 THEN 'SMALLFLOAT'
        WHEN 5 THEN 'DECIMAL'
        WHEN 6 THEN 'SERIAL'
        WHEN 7 THEN 'DATE'
        WHEN 8 THEN 'MONEY'
        WHEN 10 THEN 'DATETIME'
        WHEN 12 THEN 'TEXT'
        WHEN 13 THEN 'VARCHAR'
        WHEN 15 THEN 'NCHAR'
        WHEN 16 THEN 'NVARCHAR'
        WHEN 17 THEN 'INT8'
        WHEN 18 THEN 'SERIAL8'
        WHEN 40 THEN 'LVARCHAR'
        WHEN 41 THEN 'CLOB' -- 或 BLOB，这里统一显示为 CLOB
        ELSE 'UNKNOWN(' || MOD(c.coltype, 256) || ')'
    END ||
    -- 处理带有长度或精度的类型
    CASE
        -- CHAR, VARCHAR 等类型，显示长度
        WHEN MOD(c.coltype, 256) IN (0, 13, 15, 16, 40) 
            THEN '(' || c.collength || ')'
        -- DECIMAL, MONEY 等类型，显示精度和标度
        WHEN MOD(c.coltype, 256) IN (5, 8) 
            THEN '(' || TRUNC(c.collength / 256) || ',' || MOD(c.collength, 256) || ')'
        ELSE '' -- 其他类型不显示长度
    END) AS 字段类型,
    -- ########## 结束：数据类型转换逻辑 ##########
    CASE
        WHEN c.coltype >= 256 THEN 'NOT NULL'
        ELSE 'NULL'
    END AS 是否为空,
    sc.comments AS 表注释
FROM
    systables t
LEFT JOIN
    syscolumns c ON t.tabid = c.tabid
LEFT JOIN
    syscomments sc ON t.tabname = sc.tabname AND sc.tabtype = 'T'
LEFT JOIN
    syscolcomments scc ON t.tabname = scc.tabname AND c.colname = scc.colname
WHERE
    t.tabtype = 'T'
    AND t.owner = 'gbasedbt' -- <--- 请替换为您正确的 owner
    AND t.tabid >= 100 -- 只选择用户表
ORDER BY
    t.tabname,
    c.colno;

脚本说明

  1. 系统表关联：

    • systables: 存储表的基本信息。
    • syscolumns: 存储列的基本信息。
    • syscomments: 存储表的注释信息（注意 tabtype = 'T'）。
    • syscolcomments: 存储列的注释信息。

  2. 数据类型转换：

    • GBASE-8S 的 coltype 存储的是数字代码，我们需要通过 CASE WHEN 语句将其转换为可读的字符串（如 VARCHAR, INTEGER 等）。
    • MOD(c.coltype, 256) 用于获取基本类型代码，因为 coltype 的高位可能包含 NOT NULL 等标志。
    • 对于 DECIMAL 和 MONEY 类型，其精度和标度存储在 collength 中，需要通过计算 TRUNC(c.collength / 256) 和 MOD(c.collength, 256) 来获取。

  3. 过滤条件：

    • t.tabtype = 'T': 只查询表，排除视图等其他对象。
    • t.tabid >= 100: 通常 ID 小于 100 的是系统内部表，我们只关心用户创建的表。
    • t.owner = 'gbasedbt': 重要，请根据实际情况修改为您的数据库所有者用户名，否则可能查不到数据或查到其他用户的表。

希望这个脚本能帮助大家更方便地管理 GBASE-8S 数据库文档！