ORACLE-查询数据字典表结构SQL导出EXCEL

最新推荐文章于 2025-03-12 08:58:07 发布

chaobaojun

最新推荐文章于 2025-03-12 08:58:07 发布

阅读量1.3k

点赞数 2

文章标签： oracle 表结构 excel

本文链接：https://blog.youkuaiyun.com/chaobaojun/article/details/84579018

版权

本文提供了一个详细的SQL查询示例，用于从Oracle数据库中获取表结构的详细信息，包括表名、字段名、数据类型、是否允许为空、字段注释及默认值等。此查询对于理解和维护数据库结构非常有用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT t1.Table_Name AS "表名",
       t3.comments AS "表说明",
       t1.Column_Name AS "字段名称",
       t1.COLUMN_ID AS "字段序号",
       t1.DATA_TYPE || CASE
         WHEN t1.DATA_TYPE IN ('VARCHAR2', 'CHAR', 'NVARCHAR2') then
          '(' || t1.DATA_LENGTH || ')'
         when t1.DATA_TYPE IN ('NUMBER') AND t1.DATA_PRECISION > 0 then
          '(' || t1.DATA_PRECISION || ',' || t1.DATA_SCALE || ')'
         when t1.DATA_TYPE IN ('DATE', 'BLOB', 'TIMESTAMP(6)', 'UNDEFINED') then
          ''
         else
          ''
       end AS "数据类型",
       t1.NullAble AS "是否为空",
       t2.Comments AS "字段说明",
       t1.Data_Default "默认值",
       T1.NULLABLE "是否为空"
--t4.created                                  AS "建表时间"
FROM cols t1
LEFT JOIN user_col_comments t2
    ON t1.Table_name = t2.Table_name
   AND t1.Column_Name = t2.Column_Name
LEFT JOIN user_tab_comments t3
    ON t1.Table_name = t3.Table_name
LEFT JOIN user_objects t4
    ON t1.table_name = t4.OBJECT_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;