Oracle查询表结构、索引和备注

-- 查询Oracle表的结构，包括列名、数据类型、长度、是否可以为空和注释
SELECT
    col.table_name,
    col.column_name,
    col.data_type ||
    CASE WHEN col.data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN '(' || col.char_length || ')'
         WHEN col.data_type IN ('NUMBER') AND col.data_precision IS NOT NULL THEN '(' || col.data_precision || ',' || col.data_scale || ')'
         WHEN col.data_type IN ('FLOAT') AND col.data_precision IS NOT NULL THEN '(' || col.data_precision || ')'
         ELSE '' END AS data_type,
    col.nullable,
    com.comments AS column_comments
FROM
    user_tab_columns col
    LEFT JOIN user_col_comments com ON col.table_name = com.table_name AND col.column_name = com.column_name
WHERE
    col.table_name = 'YOUR_TABLE_NAME' -- 替换为你的表名
ORDER BY
    col.column_id;

-- 查询Oracle表的索引信息
SELECT
    ind.table_name,
    ind.index_name,
    ind.index_type,
    indc.column_name
FROM
    user_indexes ind
    JOIN user_ind_columns indc ON ind.index_name = indc.index_name
                                AND ind.table_name = indc.table_name
WHERE
    ind.table_name = 'YOUR_TABLE_NAME' -- 替换为你的表名
ORDER BY
    ind.index_name,
    indc.column_position;

-- 查询Oracle表的注释信息
SELECT
    com.table_name,
    com.comments
FROM
    user_tab_comments com
WHERE
    com.table_name = 'YOUR_TABLE_NAME' -- 替换为你的表名