如何查询Oracle表列的注释,查询oracle的表列信息以及注释信息

1、查询表和列的注释信息

select t3.table_name,

t3.comments,

t1.COLUMN_NAME,

t2.comments,

t1.DATA_TYPE || '(' || decode(t1.DATA_TYPE,

'',

t1.DATA_PRECISION + t1.DATA_SCALE,

t1.data_length) || ')'

from user_tab_cols t1, user_col_comments t2, user_tab_comments t3

where t1.TABLE_NAME = t2.table_name(+)

and t1.COLUMN_NAME = t2.column_name(+)

and t1.TABLE_NAME = t3.table_name(+)

and lower(t1.TABLE_NAME) = 'user';

执行该语句使用当前用户执行；

在PL/SQLdeveloper中查询出来之后，全选右键选择导出到excel即可。

2、查询创建表的语句

set pagesize 0

set long 90000

set echo off

spool getdll.sql

select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual;

第一个参数：object_type,第二个参数：object_name，第三个参数：schema

select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual;

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT') from dual;

spool off;

若是某个schema下的对象则：

set pagesize 0

set long 90000

set echo off

spool getdll.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('VIEW',u.VIEW_name) FROM USER_VIEWS u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

spool off;

获取某个SCHEMA的建全部对象(包括存储过程、包、函数、)的语法

select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from  user_objects u where  object_type = 'PROCEDURE';

select DBMS_METADATA.GET_DDL('SEQUENCE',u.object_name) from  user_objects u where  object_type = 'SEQUENCE';

select DBMS_METADATA.GET_DDL('TRIGGER',u.object_name) from  user_objects u where  object_type = 'TRIGGER';

select DBMS_METADATA.GET_DDL('PACKAGE',u.object_name) from  user_objects u where  object_type = 'PACKAGE';

select DBMS_METADATA.GET_DDL('FUNCTION',u.object_name) from user_objects u where object_type = 'FUNCTION';

dbms_metadata.get_dll的用法

--GET_DDL: Return the metadata for a single object as DDL.

-- This interface is meant for casual browsing (e.g., from SQLPlus)

-- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.

-- PARAMETERS:

-- object_type - The type of object to be retrieved.

-- name - Name of the object.

-- schema - Schema containing the object. Defaults to

-- the caller's schema.

-- version - The version of the objects' metadata.

-- model - The object model for the metadata.

-- transform. - XSL-T transform. to be applied.

-- RETURNS: Metadata for the object transformed to DDL as a CLOB.

FUNCTION get_ddl ( object_type IN VARCHAR2,

name IN VARCHAR2,

schema IN VARCHAR2 DEFAULT NULL,

version IN VARCHAR2 DEFAULT 'COMPATIBLE',

model IN VARCHAR2 DEFAULT 'ORACLE',

transform. IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;

3、导出

expdp system/oracle directory=exp_dir dumpfile=getdll.dmp content=metadata_only schemas=etarde log=logfile.log

4、查询一个用户的所有表的结构信息的SQL语句

select a.table_name as 表名,user_tab_comments.comments as 表名备注,a.column_name as 列名,   a.data_type as 类型,   decode(a.data_type, 'NUMBER', a.data_precision, a.data_length) as 长度, a.data_scale as 小数位数,   f.comments as 注释,   a.nullable as 是否允许空,   a.data_default as 缺省值,   decode(e.key, 'Y', 'Y', 'N') as 外键   from user_tab_columns a,user_col_comments f,user_tab_comments,    (select b.table_name,   b.index_name,   b.uniqueness,   c.column_name,   decode(d.constraint_name, 'R', 'Y', 'N') key   from user_indexes b,   user_ind_columns c,   (select constraint_name   from user_constraints   where constraint_type = 'P') d   where b.index_name = c.index_name   and b.index_name = d.constraint_name(+)) e  where a.table_name = e.table_name(+)   and a.column_name = e.column_name(+)   and a.table_name = f.table_name   and a.column_name = f.column_name   and a.table_name = user_tab_comments.table_name   --and a.table_name = 'T_EDR_CON' --and a.table_name not in('PLAN_TABLE','T_COMMAND_PARAM')  order by a.table_name