Oracle 导出表结构(含列名、数据类型、字段备注注释)
😂 这篇文章最后更新于1265天前,您需要注意相关的内容是否还可用。
SELECT
--t1.Table_Name || chr(13) || t3.comments AS "表名称及说明",
ROWNUM ,
t1.Column_Name AS "字段名称",
t1.DATA_TYPE AS "数据类型",
t1.DATA_LENGTH AS "长度",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"
--t4.created AS "建表时间"
--t3.comments 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)
and t1.TABLE_NAME='你要查询的表名'
ORDER BY t1.Table_Name, t1.Column_ID;
或
select distinct TABLE_COLUMN.*,
TABLE_NALLABLE.DATA_TYPE,
TABLE_NALLABLE.NULLABLE
from (select distinct utc.table_name table_name,
utc.comments table_comments,
ucc.column_name column_name,
ucc.comments column_comments
from user_tab_comments utc, user_col_comments ucc
where utc.table_name = ucc.table_name
and utc.table_name not like '%_B'
and utc.table_name not like '%_Z'
and utc.table_name not like '%1%') TABLE_COLUMN,
(select distinct table_name, column_name, nullable, DATA_TYPE
from user_tab_cols
where table_name not like '%_B'
and table_name not like '%_Z'
and table_name not like '%1%') TABLE_NALLABLE
where TABLE_COLUMN.column_name = TABLE_NALLABLE.column_name
and TABLE_COLUMN.TABLE_NAME = TABLE_NALLABLE.table_name;
参考
https://blog.csdn.net/liu_yulong/article/details/83619122
https://blog.csdn.net/prefectjava/article/details/37908003
留言评论
暂无留言