SELECT COL.TABLE_NAME AS "表名",
TT.COMMENTS AS "表描述",
COL.COLUMN_NAME AS "字段名",
DECODE(COL.CHAR_LENGTH,0,DECODE(COL.DATA_SCALE,NULL,COL.DATA_TYPE,COL.DATA_TYPE||'('||COL.DATA_PRECISION||','||COL.DATA_SCALE||')'),
COL.DATA_TYPE||'('||COL.CHAR_LENGTH||')') AS "字段类型",
COL.DATA_DEFAULT AS "默认值",
COL.NULLABLE AS "是否可为空",
CCOM.COMMENTS AS "字段描述"
FROM USER_TAB_COLUMNS COL,
USER_COL_COMMENTS CCOM,
(SELECT AA.TABLE_NAME,
AA.INDEX_NAME,
AA.COLUMN_NAME,
AA.COLUMN_POSITION
FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB
WHERE BB.CONSTRAINT_TYPE = 'P'
AND AA.TABLE_NAME = BB.TABLE_NAME
AND AA.INDEX_NAME = BB.CONSTRAINT_NAME
) PKCOL,
USER_TAB_COMMENTS TT
WHERE COL.TABLE_NAME = CCOM.TABLE_NAME
AND COL.COLUMN_NAME = CCOM.COLUMN_NAME
AND COL.TABLE_NAME = TT.TABLE_NAME(+)
AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)
AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)
AND COL.TABLE_NAME like '某个表名'
ORDER BY COL.TABLE_NAME,col.column_id
SELECT
COLUMN_NAME 字段名称,
COLUMN_TYPE 字段类型,
COLUMN_DEFAULT 默认值,
CHARACTER_MAXIMUM_LENGTH AS 最大长度,
(
CASE
WHEN is_nullable = 'NO' THEN
'否'
ELSE
'是'
END
) AS 是否可空,
(
CASE
WHEN column_key = 'PRI' THEN
'是'
ELSE
'否'
END
) AS 是否主键,
COLUMN_COMMENT 描述
FROM
INFORMATION_SCHEMA. COLUMNS
WHERE
table_schema = 'simulation_platform'
AND table_name = 'task'