--这里是按你的格式取得一张表的定义表格,如果要取所有表可自己改一下 SELECT t.COLUMN_NAME 字段名称, t.DATA_TYPE 类型, DECODE(m.COLUMN_NAME, NULL, 'N', 'Y') 主键PK, t.NULLABLE 允许为空 FROM user_tab_columns t, (SELECT a.TABLE_NAME, b.COLUMN_NAME FROM user_constraints a, user_cons_columns b WHERE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND a.CONSTRAINT_TYPE = 'P') m WHERE t.table_name = '&TABLE_NAME' AND t.column_name = m.column_name(+) AND t.TABLE_NAME = m.table_name(+);
--这个好一点 SELECT t.COLUMN_NAME 字段名称, t.DATA_TYPE || CASE WHEN T.DATA_TYPE = 'DATE' THEN NULL WHEN T.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN '(' || t.DATA_LENGTH || ')' WHEN T.DATA_TYPE = 'NUMBER' AND nvl(t.DATA_SCALE, 0) = 0 THEN '(' || t.DATA_PRECISION || ')' WHEN T.DATA_TYPE = 'NUMBER' AND nvl(t.DATA_SCALE, 0) <> 0 THEN '(' || t.DATA_PRECISION || ',' || t.DATA_SCALE || ')' ELSE '(' || t.DATA_LENGTH || ')' END 类型, DECODE(m.COLUMN_NAME, NULL, 'N', 'Y') 主键PK, t.NULLABLE 允许为空 FROM user_tab_columns t, (SELECT a.TABLE_NAME, b.COLUMN_NAME FROM user_constraints a, user_cons_columns b WHERE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND a.CONSTRAINT_TYPE = 'P') m WHERE t.table_name = '&TABLE_NAME' AND t.column_name = m.column_name(+) AND t.TABLE_NAME = m.table_name(+);
SELECT t.COLUMN_NAME 字段名称,
t.DATA_TYPE 类型,
DECODE(m.COLUMN_NAME, NULL, 'N', 'Y') 主键PK,
t.NULLABLE 允许为空
FROM user_tab_columns t,
(SELECT a.TABLE_NAME, b.COLUMN_NAME
FROM user_constraints a, user_cons_columns b
WHERE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND
a.CONSTRAINT_TYPE = 'P') m
WHERE t.table_name = '&TABLE_NAME' AND
t.column_name = m.column_name(+) AND
t.TABLE_NAME = m.table_name(+);
SELECT t.COLUMN_NAME 字段名称,
t.DATA_TYPE || CASE
WHEN T.DATA_TYPE = 'DATE' THEN
NULL
WHEN T.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN
'(' || t.DATA_LENGTH || ')'
WHEN T.DATA_TYPE = 'NUMBER' AND nvl(t.DATA_SCALE, 0) = 0 THEN
'(' || t.DATA_PRECISION || ')'
WHEN T.DATA_TYPE = 'NUMBER' AND nvl(t.DATA_SCALE, 0) <> 0 THEN
'(' || t.DATA_PRECISION || ',' || t.DATA_SCALE || ')'
ELSE
'(' || t.DATA_LENGTH || ')'
END 类型,
DECODE(m.COLUMN_NAME, NULL, 'N', 'Y') 主键PK,
t.NULLABLE 允许为空
FROM user_tab_columns t,
(SELECT a.TABLE_NAME, b.COLUMN_NAME
FROM user_constraints a, user_cons_columns b
WHERE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND
a.CONSTRAINT_TYPE = 'P') m
WHERE t.table_name = '&TABLE_NAME' AND
t.column_name = m.column_name(+) AND
t.TABLE_NAME = m.table_name(+);