other

DM达梦数据库导出数据库表清单与数据表结构

-- 数据表清单

SELECT 
    OWNER AS 库名,
    TABLE_NAME AS 表名,
    COMMENTS AS 表注释
FROM 
    ALL_TAB_COMMENTS
WHERE 
    OWNER = sys_context('USERENV', 'CURRENT_SCHEMA')
    AND TABLE_TYPE = 'TABLE'
ORDER BY 
    OWNER, TABLE_NAME;
 

-- 数据表结构
SELECT 
    t.OWNER AS 库名,
    t.TABLE_NAME AS 表名,
    t.COMMENTS AS 表注释,
    c.COLUMN_NAME AS 字段名,
    cc.COMMENTS AS 字段注释,  -- 修改为COMMENTS
    c.DATA_TYPE AS 字段类型,
    CASE 
        WHEN c.DATA_TYPE IN ('VARCHAR', 'CHAR', 'VARCHAR2', 'CHAR2', 'NVARCHAR', 'NCHAR') THEN c.CHAR_LENGTH
        WHEN c.DATA_TYPE IN ('NUMBER', 'NUMERIC', 'DECIMAL') THEN 
            CASE 
                WHEN c.DATA_PRECISION IS NULL THEN NULL
                WHEN c.DATA_SCALE IS NULL OR c.DATA_SCALE = 0 THEN c.DATA_PRECISION
                ELSE c.DATA_PRECISION || ',' || c.DATA_SCALE
            END
        WHEN c.DATA_TYPE IN ('DATE', 'TIMESTAMP') THEN NULL
        ELSE c.DATA_LENGTH
    END AS 字段长度,
    CASE WHEN c.NULLABLE = 'Y' THEN '是' ELSE '否' END AS 是否为空,
    c.DATA_DEFAULT AS 默认值
FROM 
    ALL_TAB_COMMENTS t
JOIN 
    ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN 
    ALL_COL_COMMENTS cc ON c.OWNER = cc.OWNER AND c.TABLE_NAME = cc.TABLE_NAME AND c.COLUMN_NAME = cc.COLUMN_NAME
WHERE 
    t.OWNER = sys_context('USERENV', 'CURRENT_SCHEMA')
    AND t.TABLE_TYPE = 'TABLE'
ORDER BY 
    t.OWNER, t.TABLE_NAME, c.COLUMN_ID;