如何读oracle数据库中的所有表的表名,每个表的字段,字段类型,长度,注释 ?
解决方案 »
- 求教语法问题哎
- oracle断电 报ora-01033:oracle initialization or shutdown in progress
- 形如'1,234,567,345.00'的字符串如何转化成NUMBER?
- 大神们,帮帮忙,非常急,无异常,插入不进数据
- 关于substr函数
- 数据库启动问题
- 大家来讨论这个问题
- orcale单行子查询返回多行
- Oracle10+:关于定时给表做分析的设计(实际应用)【对原先的代码进行进一步修改】
- 触发器:当前行有更新,则修改当前行的某个字段。调用报错
- 发现在Users组权限下运行OraClient10g_home1(Windows32位版本)会报错,请求解决。
- 好象是游标的问题
把这些值记录在一个表中
在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;