数据库文档数据表、字段信息查询脚本（适用oracle）

数据库文档数据表、字段信息查询脚本（适用oracle）

–适用于ORACLE数据库

00：公共方法（需要先执行）

----00：公共方法（需要先执行）
create or replace function LONG_TO_CHAR(p_table_name in varchar2,p_column_name in varchar2) return varchar2 is
l_column_comments varchar2(32767) default null;
begin
  SELECT dt.DATA_DEFAULT
  INTO  l_column_comments
  FROM all_Tab_Columns dt 
  WHERE 1=1
  and dt.TABLE_NAME = UPPER(p_table_name)
  and dt.COLUMN_NAME = UPPER(p_column_name);
  
  l_column_comments := substr(l_column_comments,0,240);
  return l_column_comments;
exception
  when others then
    return l_column_comments;
end LONG_TO_CHAR;

01：数据字典：传统

-----01：数据字典：传统------------------------------------------------------------------------
SELECT dth.TABLE_NAME,--表名
         dtl.COLUMN_NAME,--列名
         dtlc.comments,--字段描述
         dtl.DATA_TYPE,--字段类型
         dtl.DATA_LENGTH,--字段长度
         dtl.NULLABLE,--是否可为空
         nvl((select 'Y'
          from
           DBA_constraints con,
           DBA_cons_columns col
          where
           con.constraint_name=col.constraint_name
           and con.table_name = dth.TABLE_NAME
           and col.column_name = dtl.COLUMN_NAME
           and con.owner = dth.OWNER
           and con.constraint_type in ('P','R')
           GROUP BY col.column_name),'N') constraints_type,--是否主外键
         LONG_TO_CHAR(dth.TABLE_NAME,dtl.COLUMN_NAME) DATA_DEFAULT--默认值
FROM DBA_TABLES  dth,
         dba_tab_comments dthc,
         Dba_Tab_Columns dtl,
         Dba_Col_Comments dtlc
         WHERE 1=1
         and dth.TABLE_NAME = dthc.table_name
         and dth.OWNER = dthc.owner
         and dthc.table_type = 'TABLE'
         and dth.TABLE_NAME = dtl.TABLE_NAME
         and dth.OWNER = dtl.OWNER
         and dtl.TABLE_NAME = dtlc.table_name
         and dtl.OWNER = dtlc.owner
         and dtl.COLUMN_NAME = dtlc.column_name
         --参数：用户名称（大写）
         and dth.OWNER in ();

02：表清单-

-------02：表清单------------------------------------------------------------------------
         SELECT dth.TABLE_NAME,--表名
                     dthc.comments--表备注
         FROM DBA_TABLES  dth,
         dba_tab_comments dthc
         WHERE 1=1
         and dth.TABLE_NAME = dthc.table_name
         and dth.OWNER = dthc.owner
         and dthc.table_type = 'TABLE'
         --参数：用户名称（大写）
         and dth.OWNER in ();