Oracle导出表结构至Excel

xiaoxiao2021-02-28  113

为了整理复杂的系统数据表结构,方便以后研发人员工作,特此整理数据字典。

oracle表结构涉及到几个关键的系统表:

cols –系统表

user_col_comments –表名、列名、说明

user_tab_comments –表名、类型、说明

user_objects – 字典表视图

真正导出的SQL语句整理如下:

SELECT t1.Table_Name || chr(13) || t3.comments AS "表名称及说明", --t3.comments AS "表说明", t1.Column_Name AS "字段名称", t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')' AS "数据类型", t1.NullAble AS "是否为空", t2.Comments AS "字段说明", t1.Data_Default "默认值" --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;

直接将结果导出至Excel文件即可。

转载请注明原文地址: https://www.6miu.com/read-47004.html

最新回复(0)