SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
DECODE(A.NULLABLE,'N','不可','Y','可'),
A.COLUMN_ID,
D.COLUMN_POSITION AS PRIMARY_KEY,
E.COMMENTS,
A.TABLE_NAME,
F.COMMENTS
FROM
USER_TAB_COLUMNS A,
(
SELECT
C.TABLE_NAME,
C.COLUMN_NAME,
C.COLUMN_POSITION
FROM
USER_CONSTRAINTS B,
USER_IND_COLUMNS C
WHERE
B.CONSTRAINT_TYPE = 'P'
AND B.TABLE_NAME = C.TABLE_NAME
AND B.CONSTRAINT_NAME = C.INDEX_NAME
)D,
USER_COL_COMMENTS E,
USER_TAB_COMMENTS F
WHERE
A.TABLE_NAME = '&TABLE_NAME'
AND A.TABLE_NAME = D.TABLE_NAME(+)
AND A.COLUMN_NAME =D.COLUMN_NAME(+)
AND A.TABLE_NAME = E.TABLE_NAME(+)
AND A.COLUMN_NAME = E.COLUMN_NAME(+)
AND A.TABLE_NAME = F.TABLE_NAME(+)
ORDER BY
A.TABLE_NAME,
A.COLUMN_ID;
A.COLUMN_NAME,
A.DATA_TYPE,
DECODE(A.NULLABLE,'N','不可','Y','可'),
A.COLUMN_ID,
D.COLUMN_POSITION AS PRIMARY_KEY,
E.COMMENTS,
A.TABLE_NAME,
F.COMMENTS
FROM
USER_TAB_COLUMNS A,
(
SELECT
C.TABLE_NAME,
C.COLUMN_NAME,
C.COLUMN_POSITION
FROM
USER_CONSTRAINTS B,
USER_IND_COLUMNS C
WHERE
B.CONSTRAINT_TYPE = 'P'
AND B.TABLE_NAME = C.TABLE_NAME
AND B.CONSTRAINT_NAME = C.INDEX_NAME
)D,
USER_COL_COMMENTS E,
USER_TAB_COMMENTS F
WHERE
A.TABLE_NAME = '&TABLE_NAME'
AND A.TABLE_NAME = D.TABLE_NAME(+)
AND A.COLUMN_NAME =D.COLUMN_NAME(+)
AND A.TABLE_NAME = E.TABLE_NAME(+)
AND A.COLUMN_NAME = E.COLUMN_NAME(+)
AND A.TABLE_NAME = F.TABLE_NAME(+)
ORDER BY
A.TABLE_NAME,
A.COLUMN_ID;