查询用户下所有表数据字典并写入一张表导出

DECLARE

  vc_sql VARCHAR2(32767);

  v_is_exist NUMBER;

  v_seq NUMBER DEFAULT 0;

  CURSOR cur_tab IS
  
    SELECT table_name
      FROM user_tables
    
     ORDER BY table_name;

BEGIN

  SELECT COUNT(1)
    INTO v_is_exist
    FROM user_tables
   WHERE table_name = 'MY_TAB_STRC';

  IF v_is_exist = 0 THEN
  
    vc_sql := 'CREATE TABLE MY_TAB_STRC(COL1 VARCHAR(1000) ,COL2 VARCHAR2(4000), COL3 VARCHAR2(30) ,COL4 VARCHAR2(4000) ,COL5 VARCHAR2(10) ,COL6 VARCHAR2(4000),COL7 VARCHAR2(4000),TAB_SQ NUMBER(10),SQ NUMBER(10))';
  
    EXECUTE IMMEDIATE vc_sql;
  
  ELSE
  
    vc_sql := 'TRUNCATE TABLE MY_TAB_STRC';
  
    EXECUTE IMMEDIATE vc_sql;
  
  END IF;

  FOR cur IN cur_tab LOOP
  
    v_seq := v_seq + 1;
  
    vc_sql := 'INSERT INTO MY_TAB_STRC '
             
              || ' SELECT COL1 ,COL2 ,COL3 ,COL4,COL5,COL6 ,COL7 ,COL8,' ||
              v_seq
             
              || ' FROM ( '
             
              || ' SELECT ''表名称:''' || '||COMMENTS||' || '''(' ||
              cur.table_name || ')''' ||
              ' AS COL1, NULL AS COL2 ,NULL AS COL3 ,NULL AS COL4 ,NULL AS COL5,NULL AS COL6,NULL AS COL7,-2 AS COL8 '
             
              || ' FROM USER_TAB_COMMENTS WHERE TABLE_NAME=''' ||
              cur.table_name || ''''
             
              || ' UNION ALL '
             
              ||
              ' SELECT ''序号'' COL1, ''字段名称'' COL2, ''字段ID'' COL3,''数据类型'' COL4,''是否主键列'' COL5,''为空'' COL6,''备注'' COL7,-1 COL8 FROM DUAL '
             
              || ' UNION ALL '
             
              ||
              ' SELECT TO_CHAR(COLUMN_ID) COL1,SUBSTR(comMENTS,1,20) COL2,A.COLUMN_NAME COL3,'
             
              || ' DECODE(A.DATA_TYPE '
             
              ||
              ' ,''NUMBER'' ,DECODE(A.DATA_PRECISION, NULL, ''NUMBER'' ,''NUMBER('' || DATA_PRECISION || '','' || DATA_SCALE || '')'') '
             
              ||
              ' ,''VARCHAR2'' ,A.DATA_TYPE || ''('' || DATA_LENGTH || '')'' '
             
              ||
              ' ,''CHAR'' ,A.DATA_TYPE || ''('' || DATA_LENGTH || '')'' '
             
              || ' ,''DATE'' ,A.DATA_TYPE '
             
              ||
              ' ,A.DATA_TYPE) AS COL4, NULL COL5,DECODE(A.NULLABLE,''Y'',''是'',''N'',''否'') COL6'
             
              ||
              ' ,SUBSTR(comMENTS,21) AS COL7,COLUMN_ID COL8'
             
              || ' FROM USER_TAB_COLUMNS A,' || ' USER_COL_COMMENTS C '
             
              || ' WHERE A.TABLE_NAME = ''' || cur.table_name || ''' '
             
              || ' AND C.TABLE_NAME = ''' || cur.table_name || ''' '
             
              || ' AND A.TABLE_NAME = C.TABLE_NAME(+) '
             
              || ' AND A.COLUMN_NAME = C.COLUMN_NAME(+) '
             
              || ' ) T ';
  
    --DBMS_OUTPUT.PUT_LINE(VC_SQL);
  
    EXECUTE IMMEDIATE vc_sql;
  
  END LOOP;

  COMMIT;

END;