sql查询出数据库所有表名、表注释、字段、字段类型、字段注释、（用于导出表结构）

SELECT 
    CASE 
        WHEN t1.rn = 1 THEN t1.Table_Name
        ELSE ''
    END AS "表名称",
    CASE 
        WHEN t1.rn = 1 THEN t1.comments
        ELSE ''
    END AS "表说明",
    t1.Data_Type AS "数据类型",
    t1.Column_Name AS "字段名称",
    t2.Comments AS "字段说明"
FROM (
    SELECT 
        t1.Table_Name,
        t3.comments,
        t1.Data_Type,
        t1.Column_Name,
        t1.Column_ID,
        ROW_NUMBER() OVER(PARTITION BY t1.Table_Name ORDER BY t1.Column_ID) AS rn
    FROM cols t1
    LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
) t1
LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
WHERE NOT EXISTS (
    SELECT t4.Object_Name FROM User_objects t4
    WHERE t4.Object_Type = 'TABLE' 
    AND t4.Temporary = 'Y' 
    AND t4.Object_Name = t1.Table_Name
)
ORDER BY t1.Table_Name, t1.Column_ID;

效果：