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;