SELECT a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length
, a.data_precision
, a.data_scale
, d.position
FROM user_tab_cols a
LEFT JOIN user_col_comments b
ON a.table_name = b.table_name
AND a.column_name = b.column_name
LEFT JOIN user_tab_comments c
ON c.table_name = b.table_name
LEFT JOIN
(SELECT ucc.table_name
, ucc.column_name
, ucc.position
FROM user_cons_columns ucc
JOIN user_constraints uc
ON ucc.constraint_name = uc.constraint_name
AND uc.constraint_type = UPPER('p')
) d
ON d.table_name = b.table_name
AND d.column_name = b.column_name
--where a.TABLE_NAME = 'TAB_QUALITY_INFO'
ORDER BY a.table_name
, a.column_id ASC ;
dba 用户导出
SELECT a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length
, a.data_precision
, a.data_scale
, d.position
FROM dba_tab_cols a
LEFT JOIN dba_col_comments b
ON b.table_name = a.table_name
AND b.column_name = a.column_name
AND a.owner = b.owner
LEFT JOIN dba_tab_comments c
ON c.table_name = b.table_name
AND c.owner = b.owner
LEFT JOIN
( SELECT dcc.table_name
, dcc.column_name
, dcc.position
, dcc.owner
FROM dba_cons_columns dcc
JOIN dba_constraints dc
ON dcc.constraint_name = dc.constraint_name
AND dc.constraint_type = UPPER('p')
AND dcc.owner = dc.owner
) d
ON d.table_name = b.table_name
AND d.column_name = b.column_name
AND d.owner = a.owner
WHERE a.owner = UPPER('test')
--and a.TABLE_NAME = 'TAB_QUALITY_INFO'
AND a.table_name IN ('B_RKJBXX_CUR', 'C_RKJBXX_CUR', 'DIM_ODS_RKJBXX_CUR_CODEMAP', 'DIM_RKJBXX_PRIORITY', 'JKCSB', 'ODS_RKJBXX_CUR', 'TAB_QUALITY_INFO', 'TEST', 'TEST1', 'THE_CONF', 'TMP1', 'TMP2')
ORDER BY a.table_name
, a.column_id ASC;