oracle元数据获取

-- 查询表名,表注释,字段注释
select ATC.OWNER,
       atC.TABLE_NAME,
       ucc.comments,
       ATC.COLUMN_NAME,
       ATC.DATA_TYPE,
       ATC.DATA_LENGTH,
       ATC.NULLABLE,
       ucc.comments
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_col_comments ucc on atc.table_name = ucc.table_name
                                       and atc.column_name =
                                           ucc.column_name
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;


 select * from user_tab_comments 

-- 表注释SQL
select 'comment on table ' || atC.TABLE_NAME || ' is -' ||
                utc.comments || '-;'
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;



-- 字段注释
select 'comment on column '||atC.TABLE_NAME||'.'||ATC.COLUMN_NAME||' is -'||ucc.comments||'-;'
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('USER_1')) atc
  left outer join user_col_comments ucc on atc.table_name = ucc.table_name
                                       and atc.column_name =
                                           ucc.column_name
  left outer join user_tab_comments utc on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;

`SELECT F_LIMS_GET_SQL_FOR_MYSQL('T_LIMS_EQUIPMENT') FROM DUAL;

CREATE OR REPLACE FUNCTION F_LIMS_GET_SQL_FOR_MYSQL(PI_TABLENAME IN VARCHAR2,
                                                    PI_ISDROP    IN INTEGER := 1)
  RETURN CLOB IS
  V_TABLENAME         VARCHAR(100);
  V_RET_SQL           VARCHAR2(4000);
  V_SQL_HEADER        VARCHAR2(1000);
  V_SQL_BODY          CLOB;
  V_SQL_INDEX         VARCHAR2(4000);
  V_SQL_TABLE_COMMENT VARCHAR(400);
  V_SQL_PK_COL_LIST   VARCHAR(1000);
  -- 根据当前数据库和表名，生成mysql建表语句
BEGIN
  -- 表名统一为大写
  SELECT UPPER(PI_TABLENAME) INTO V_TABLENAME FROM DUAL;

  SELECT MAX(T.COMMENTS)
    INTO V_SQL_TABLE_COMMENT
    FROM USER_TAB_COMMENTS T
   WHERE T.TABLE_NAME = V_TABLENAME;

  SELECT MAX(WM_CONCAT('`' || A.COLUMN_NAME || '` '))
    INTO V_SQL_PK_COL_LIST
    FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
   WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
     AND B.CONSTRAINT_TYPE = 'P'
     AND A.TABLE_NAME = V_TABLENAME
   GROUP BY A.TABLE_NAME;

  V_SQL_HEADER := '-- ' || V_SQL_TABLE_COMMENT || ' 建表语句' || CHR(13);

  IF PI_ISDROP = 1 THEN
    V_SQL_HEADER := V_SQL_HEADER || 'DROP TABLE IF EXISTS `' || V_TABLENAME || '`;' ||
                    CHR(13);
  END IF;
  V_SQL_HEADER := V_SQL_HEADER || 'CREATE TABLE `' || V_TABLENAME || '`  (';

  -- 表列信息
  SELECT WM_CONCAT(CHR(13) || '`' || T.COLUMN_NAME || '` ' ||
                   (CASE
                      WHEN T.DATA_TYPE = 'VARCHAR2' OR T.DATA_TYPE = 'NVARCHAR2' THEN
                       'VARCHAR(' || T.DATA_LENGTH || ')'
                      WHEN T.DATA_TYPE = 'DATE' THEN
                       'DATETIME'
                      WHEN T.DATA_TYPE = 'CLOB' THEN
                       'MEDIUMTEXT'
                      WHEN T.DATA_TYPE = 'NUMBER' THEN
                       'DECIMAL'
                      WHEN T.COLUMN_NAME = 'ID' THEN
                       'VARCHAR(32)'
                      ELSE
                       T.DATA_TYPE
                    END) || DECODE(T.NULLABLE, 'N', ' NOT NULL ', '') ||
                   ' COMMENT ''' || TC.COMMENTS || '''')
    INTO V_SQL_BODY
    FROM USER_TAB_COLUMNS T
    LEFT JOIN USER_COL_COMMENTS TC
      ON T.TABLE_NAME = TC.TABLE_NAME
     AND T.COLUMN_NAME = TC.COLUMN_NAME
   WHERE T.TABLE_NAME = V_TABLENAME
   ORDER BY T.COLUMN_ID;

  IF V_SQL_PK_COL_LIST IS NOT NULL THEN
    V_SQL_BODY := V_SQL_BODY || ' ,' || CHR(13) || ' PRIMARY KEY (' ||
                  V_SQL_PK_COL_LIST || ')  ';
  END IF;

  -- 表备注
  V_SQL_BODY := V_SQL_BODY || CHR(13) || ') COMMENT = ''' ||
                V_SQL_TABLE_COMMENT || ''';' || CHR(13);

  -- 唯一索引 
  SELECT (WM_CONCAT('@ALTER TABLE `' || A.TABLE_NAME ||
                    '` ADD UNIQUE INDEX (' ||
                    WM_CONCAT('`' || A.COLUMN_NAME || '`') || ');@'))
    INTO V_SQL_INDEX
    FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
   WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
     AND B.CONSTRAINT_TYPE = 'U'
     AND A.TABLE_NAME = V_TABLENAME
   GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME;

  IF V_SQL_INDEX IS NOT NULL THEN
    V_SQL_INDEX := REPLACE(REPLACE(V_SQL_INDEX, '@,@', CHR(13)), '@', '');
    V_SQL_INDEX := '-- 创建索引' || CHR(13) || V_SQL_INDEX;
  END IF;

  RETURN V_SQL_HEADER || V_SQL_BODY || V_SQL_INDEX;
END;

获取表：

select table_name from user_tables; //当前用户的表

select table_name from all_tables; //所有用户的表

select table_name from dba_tables; //包括系统表

select table_name from dba_tables where owner='用户名'

user_tables：

table_name,tablespace_name,last_analyzed等

dba_tables：

ower,table_name,tablespace_name,last_analyzed等

all_tables：

ower,table_name,tablespace_name,last_analyzed等

all_objects：

ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段：

select * from user_tab_columns where Table_Name='用户表';

select * from all_tab_columns where Table_Name='用户表';

select * from dba_tab_columns where Table_Name='用户表';

user_tab_columns：

table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

all_tab_columns ：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

dba_tab_columns：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

获取表注释：

select * from user_tab_comments

user_tab_comments：table_name,table_type,comments

相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。

获取字段注释：

select * from user_col_comments

user_col_comments：table_name,column_name,comments

相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。

获取主键：
select * from user_constraints 
where table_name = 'AAA' 
and constraint_type ='P'