Oracle查询表信息：表名、表注释、字段名、字段注释、默认值等

Zhang_960110

于 2025-06-06 16:51:05 发布

阅读量95

点赞数 2

CC 4.0 BY-SA版权

文章标签： oracle java 数据库

本文链接：https://blog.csdn.net/ZSA222/article/details/148478228

SELECT 
    t.table_name AS "表名",
    tc.comments AS "表备注",
    c.column_name AS "字段名",
    cc.comments AS "字段注释",
    c.data_type AS "数据类型",
    CASE 
        WHEN c.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN TO_CHAR(c.char_length)
        WHEN c.data_type IN ('NUMBER') THEN 
            CASE 
                WHEN c.data_precision IS NULL THEN NULL
                WHEN c.data_scale IS NULL OR c.data_scale = 0 THEN TO_CHAR(c.data_precision)
                ELSE TO_CHAR(c.data_precision) || ',' || TO_CHAR(c.data_scale)
            END
        ELSE NULL
    END AS "长度/精度",
    c.data_default AS "默认值",
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM all_cons_columns cc
            JOIN all_constraints con ON cc.constraint_name = con.constraint_name 
                                      AND cc.owner = con.owner
                                      AND cc.table_name = con.table_name
            WHERE con.constraint_type = 'P'
              AND cc.table_name = t.table_name
              AND cc.column_name = c.column_name
              AND cc.owner = t.owner
        ) THEN '是' 
        ELSE '否' 
    END AS "是否为主键",
    CASE 
        WHEN c.nullable = 'Y' THEN '是' 
        ELSE '否' 
    END AS "是否允许为空"
FROM 
    all_tables t
JOIN 
    all_tab_comments tc ON t.table_name = tc.table_name AND t.owner = tc.owner
JOIN 
    all_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner
LEFT JOIN 
    all_col_comments cc ON c.table_name = cc.table_name 
                        AND c.column_name = cc.column_name 
                        AND c.owner = cc.owner
WHERE 
    t.owner = '你的'
ORDER BY 
    t.table_name, 
    c.column_id;