--视图 意义:
--用到的视图 视图注释 用到的字段 注释
--USER_TAB_COMMENTS 表注释 table_name/COMMENTS 表/表注释
--USER_COL_COMMENTS 字段注释 column_name/COMMENTS 字段/字段注释
--USER_TAB_COLS 表的字段信息 NULLABLE 是否为空
SQL:
SELECT ' 表名字' TABLE_NAME,
' 表注释' , --如果'表注释'前不加空格 ORDER BY时会排序在最后
' 列名字' ,
' 列注释' ,
' 是否为空'
FROM DUAL
UNION ALL
SELECT CASE
WHEN RN_TABLE = 1 THEN
TABLE_NAME
ELSE
NULL
END TABLE_NAME,
CASE
WHEN RN_COLUMN = 1 THEN
UTC_COMMENTS
ELSE
NULL
END UTC_COMMENTS,
COLUMN_NAME,
UCC_COMMENTS,
NULLABLE
FROM ( SELECT TABLE_NAME,
UTC_COMMENTS,
COLUMN_NAME,
UCC_COMMENTS,
NULLABLE,
ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, UTC_COMMENTS ORDER BY TABLE_NAME, UTC_COMMENTS DESC) RN_TABLE,
ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, UTC_COMMENTS ORDER BY TABLE_NAME, UTC_COMMENTS DESC) RN_COLUMN
--按照TABLE_NAME, UTC_COMMENTS分组,只显示第一行 其他都显示为空
FROM (SELECT UTC.TABLE_NAME,
UTC.COMMENTS UTC_COMMENTS,
UCC.COLUMN_NAME,
UCC.COMMENTS UCC_COMMENTS,
--DATA_TYPE,
UTC1.NULLABLE
FROM USER_TAB_COMMENTS UTC
JOIN USER_COL_COMMENTS UCC
ON UTC.TABLE_NAME = UCC.TABLE_NAME
JOIN USER_TAB_COLS UTC1
ON UTC.TABLE_NAME = UTC1.TABLE_NAME
AND UTC1.COLUMN_NAME = UCC.COLUMN_NAME
WHERE UTC1.TABLE_NAME = UTC.TABLE_NAME
AND UTC1.TABLE_NAME NOT IN
( SELECT UET.TABLE_NAME FROM USER_EXTERNAL_TABLES UET) --筛选掉外部表
/* AND UTC1.TABLE_NAME NOT IN
(SELECT TABLE_NAME
FROM USER_TABLES T
WHERE T.TEMPORARY = 'Y')*/ --筛选掉临时表
))
--ORDER BY TABLE_NAME;
祝好~
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1259733/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-1259733/