oracle 自动生成数据字典,ORACLE自动生成全库数据字典

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(C.COMMENTS,1,10) 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(C.COMMENTS,11) 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; /