現在的工作性質是全職SA,海量的文件寫下來,什麼都要自己手工刻也太辛苦,於是,網路上搜了現成語法,自己微調了下需要的欄位和查詢條件,方便自己能快速出相關文件。
已經忘了出處了,純留底以便自己存查!
當然,這語法好不好用,也取決於DBA夠不夠勤勞,如果DBA夠勤勞,把Table和欄位名稱都有註解上去,那這語法就會超好用!
唯一可惜的是,SP、Function、Package......等等還沒搜到合適的語法可以用,只能哪天有閒心時,再看是不是自己來生語法了!
SELECT
-- C.OWNER,
C.TABLE_NAME,
TC.COMMENTS,
-- C.COLUMN_ID,
C.COLUMN_NAME,
CC.COMMENTS DATA_TYPE,
-- DATA_LENGTH,
-- DATA_PRECISION,
-- DATA_DEFAULT,
NULLABLE
FROM ALL_TAB_COLUMNS C
JOIN ALL_TABLES T
ON C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN ALL_TAB_COMMENTS TC
ON TC.OWNER = T.OWNER
AND TC.TABLE_NAME = T.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS CC
ON C.OWNER = CC.OWNER
AND C.TABLE_NAME = CC.TABLE_NAME
AND C.COLUMN_NAME = CC.COLUMN_NAME
WHERE 1 = 1
-- AND C.OWNER = 'SCHEMA_NAME'
-- AND UPPER(C.TABLE_NAME) = UPPER('&TABLE_NAME')
AND UPPER(C.TABLE_NAME) LIKE UPPER('%&TABLE_NAME%')
-- AND UPPER(C.COLUMN_NAME) = UPPER('&COLUMN_NAME')
AND UPPER(C.COLUMN_NAME) LIKE UPPER('%&COLUMN_NAME%')
ORDER BY C.TABLE_NAME, C.COLUMN_ID
沒有留言:
張貼留言