查询数据字典生成表字段信息，支持MySQL，Oracle，DB2

开发初期需要了解表功能和表关系，为了便于观察，我习惯把表结构平铺展示。于是写了句查询SQL，通过数据字典生成表的字段信息。

把SQL查询结果复制到文本编辑器中
效果如下

MySQL实现

# MYSQL版本，需要MySQL 8以上
WITH RECURSIVE
    tab_info(TABLE_NAME, TABLE_SCHEMA) AS (
#     在这里输入表名称和schema名称
        SELECT 'jimu_report', 'jeecg-boot'
    ),
    col_info(COLNAME, COL_LEN, TYPE, TYPE_LEN, REMARKS) AS (
        SELECT COLNAME, LENGTH(COLNAME) COL_LEN, TYPE, LENGTH(TYPE) TYPE_LEN, REMARKS
        FROM (
                 SELECT COLUMN_NAME COLNAME, COLUMN_TYPE TYPE, COLUMN_COMMENT REMARKS
                 FROM information_schema.COLUMNS columns,
                      tab_info
                 WHERE columns.TABLE_NAME = tab_info.TABLE_NAME
                   AND columns.TABLE_SCHEMA = tab_info.TABLE_SCHEMA
                 ORDER BY ORDINAL_POSITION
             ) columns
    ),
    max_colname_len(LEN1) AS (
        SELECT MAX(COL_LEN) + 4
        FROM col_info
    ),
    max_type_len(LEN2) AS (
        SELECT MAX(TYPE_LEN) + 4
        FROM col_info
    ),
    blank_repository(NUM, BLANK) AS (
        SELECT 0, CAST('' AS CHAR(40))
        UNION ALL
        SELECT NUM + 1, CONCAT(BLANK, CHAR(32))
        FROM blank_repository
        WHERE NUM < 40
    )
SELECT TABLE_COMMENT, NULL, NULL
FROM information_schema.TABLES tables,
     tab_info
WHERE tables.TABLE_NAME = tab_info.TABLE_NAME
  AND tables.TABLE_SCHEMA = tab_info.TABLE_SCHEMA
  AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT TABLE_NAME, NULL, NULL
FROM tab_info
UNION ALL
SELECT CONCAT(COLNAME, (SELECT BLANK FROM blank_repository WHERE NUM = LEN1 - COL_LEN)) COLNAME,
       CONCAT(TYPE, (SELECT BLANK FROM blank_repository WHERE NUM = LEN2 - TYPE_LEN))   TYPE,
       REMARKS
FROM col_info,
     max_colname_len,
     max_type_len;

Oracle实现

-- Oracle版本，需要Oracle 11以上
WITH tab_info(TABLE_NAME, TABSCHEMA) AS (
--  在这里输入表名称和schema名称
    SELECT 'T_USER', 'TEST'
    FROM dual
),
     col_info(COLNAME, COL_LEN, TYPE, TYPE_LEN, REMARKS) AS (
         SELECT COLNAME, LENGTH(COLNAME) COL_LEN, TYPE, LENGTH(TYPE) TYPE_LEN, REMARKS
         FROM (
                  SELECT columns.COLUMN_NAME                                                               COLNAME,
                         DATA_TYPE || '(' || DATA_LENGTH || NVL2(DATA_SCALE, ',' || DATA_SCALE, '') || ')' TYPE,
                         remark.COMMENTS                                                                   REMARKS
                  FROM SYS.ALL_TAB_COLUMNS columns
                           INNER JOIN tab_info
                                      ON tab_info.TABLE_NAME = columns.TABLE_NAME
                                          AND tab_info.TABSCHEMA = columns.OWNER
                           INNER JOIN SYS.ALL_COL_COMMENTS remark
                                      ON remark.TABLE_NAME = tab_info.TABLE_NAME
                                          AND remark.COLUMN_NAME = columns.COLUMN_NAME
                                          AND remark.OWNER = tab_info.TABSCHEMA
                  ORDER BY COLUMN_ID
              )
     ),
     max_colname_len(LEN1) AS (
         SELECT MAX(COL_LEN) + 4
         FROM col_info
     ),
     max_type_len(LEN2) AS (
         SELECT MAX(TYPE_LEN) + 4
         FROM col_info
     ),
     blank_repository(NUM, BLANK) AS (
         SELECT 0, CAST('' AS VARCHAR(40))
         FROM dual
         UNION ALL
         SELECT NUM + 1, BLANK || CHR(32)
         FROM blank_repository
         WHERE NUM < 40
     )
SELECT COMMENTS, NULL, NULL
FROM SYS.ALL_TAB_COMMENTS tables,
     tab_info
WHERE tables.TABLE_NAME = tab_info.TABLE_NAME
  AND tables.OWNER = tab_info.TABSCHEMA
  AND TABLE_TYPE = 'TABLE'
UNION ALL
SELECT TABLE_NAME, NULL, NULL
FROM tab_info
UNION ALL
SELECT COLNAME || (SELECT BLANK FROM blank_repository WHERE NUM = LEN1 - COL_LEN) COLNAME,
       TYPE || (SELECT BLANK FROM blank_repository WHERE NUM = LEN2 - TYPE_LEN)   TYPE,
       REMARKS
FROM col_info,
     max_colname_len,
     max_type_len;

DB2实现

-- DB2版本
WITH tab_info(TABLE_NAME, TABSCHEMA) AS (
--  在这里输入表名称和schema名称
    SELECT 'T_USER', 'TEST'
    FROM SYSIBM.DUAL
),
     col_info(COLNAME, COL_LEN, TYPE, TYPE_LEN, REMARKS) AS (
         SELECT COLNAME, LENGTH(COLNAME) COL_LEN, TYPE, LENGTH(TYPE) TYPE_LEN, REMARKS
         FROM (
                  SELECT COLNAME, TYPENAME || '(' || LENGTH || ',' || SCALE || ')' TYPE, REMARKS
                  FROM SYSCAT.COLUMNS columns,
                       tab_info
                  WHERE columns.TABNAME = tab_info.TABLE_NAME
                    AND columns.TABSCHEMA = tab_info.TABSCHEMA
                  ORDER BY COLNO
              )
     ),
     max_colname_len(LEN1) AS (
         SELECT MAX(COL_LEN) + 4
         FROM col_info
     ),
     max_type_len(LEN2) AS (
         SELECT MAX(TYPE_LEN) + 4
         FROM col_info
     ),
     blank_repository(NUM, BLANK) AS (
         VALUES (0, CAST('' AS VARCHAR(40)))
         UNION ALL
         SELECT NUM + 1, BLANK || CHR(32)
         FROM blank_repository
         WHERE NUM < 40
     )
SELECT REMARKS, NULL, NULL
FROM SYSCAT.TABLES tables,
     tab_info
WHERE tables.TABNAME = tab_info.TABLE_NAME
  AND tables.TABSCHEMA = tab_info.TABSCHEMA
  AND TYPE = 'T'
UNION ALL
SELECT TABLE_NAME, NULL, NULL
FROM tab_info
UNION ALL
SELECT COLNAME || (SELECT BLANK FROM blank_repository WHERE NUM = LEN1 - COL_LEN) COLNAME,
       TYPE || (SELECT BLANK FROM blank_repository WHERE NUM = LEN2 - TYPE_LEN)   TYPE,
       REMARKS
FROM col_info,
     max_colname_len,
     max_type_len;