select * from user_tables可以得到所有的表名; 在sqlplus下用desc 表名 可以查看标的结构
表名 SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='TABLE_OWNER' 字段名 SELECT COLUMN_NAME,DATA_LENGTH,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='TABLE_NAME'
Create table 这里输入结果表名 as SELECT cols.owner, cols.table_name, cols.column_name AS NAME, data_type AS TYPE, DECODE (data_type, 'NUMBER', data_precision + data_scale, data_length ) LENGTH, data_precision PRECISION, data_scale scale, comments FROM SYS.all_col_comments coms, SYS.all_tab_columns cols WHERE coms.table_name = cols.table_name AND coms.column_name = cols.column_name AND cols.owner = '这里输入用户名' ORDER BY cols.owner, cols.table_name, column_id;这么负责任的答案在不给分说不过去了啊。
SELECT cols.owner, cols.table_name, cols.column_name AS NAME, data_type AS TYPE, DECODE (data_type, 'NUMBER ', data_precision + data_scale, data_length ) LENGTH, data_precision PRECISION, data_scale scale, comments, NVL2 (cons.column_name, 'PK', NULL) 是否PK FROM SYS.all_col_comments coms, (SELECT cc.owner, cc.column_name, cc.table_name FROM all_constraints con, dba_cons_columns cc WHERE con.owner = '这里输入用户名' AND con.owner = cc.owner AND con.table_name = cc.table_name AND con.constraint_type = 'P' AND con.constraint_name = cc.constraint_name) cons, SYS.all_tab_columns cols WHERE coms.table_name = cols.table_name AND coms.column_name = cols.column_name AND cols.owner = '这里输入用户名' AND coms.column_name = cons.column_name(+) AND coms.table_name = cons.table_name(+) AND coms.owner = cons.owner(+) ORDER BY cols.owner, cols.table_name, column_id;
把这些值记录在一个表中
在sqlplus下用desc 表名 可以查看标的结构
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='TABLE_OWNER'
字段名
SELECT COLUMN_NAME,DATA_LENGTH,DATA_TYPE
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME='TABLE_NAME'
SELECT cols.owner, cols.table_name, cols.column_name AS NAME,
data_type AS TYPE,
DECODE (data_type,
'NUMBER', data_precision + data_scale,
data_length
) LENGTH,
data_precision PRECISION, data_scale scale, comments
FROM SYS.all_col_comments coms, SYS.all_tab_columns cols
WHERE coms.table_name = cols.table_name
AND coms.column_name = cols.column_name
AND cols.owner = '这里输入用户名'
ORDER BY cols.owner, cols.table_name, column_id;这么负责任的答案在不给分说不过去了啊。
data_type AS TYPE,
DECODE (data_type,
'NUMBER ', data_precision + data_scale,
data_length
) LENGTH,
data_precision PRECISION, data_scale scale, comments,
NVL2 (cons.column_name, 'PK', NULL) 是否PK
FROM SYS.all_col_comments coms,
(SELECT cc.owner, cc.column_name, cc.table_name
FROM all_constraints con, dba_cons_columns cc
WHERE con.owner = '这里输入用户名'
AND con.owner = cc.owner
AND con.table_name = cc.table_name
AND con.constraint_type = 'P'
AND con.constraint_name = cc.constraint_name) cons,
SYS.all_tab_columns cols
WHERE coms.table_name = cols.table_name
AND coms.column_name = cols.column_name
AND cols.owner = '这里输入用户名'
AND coms.column_name = cons.column_name(+)
AND coms.table_name = cons.table_name(+)
AND coms.owner = cons.owner(+)
ORDER BY cols.owner, cols.table_name, column_id;