获取Oracle10g表元数据

shenyg

spool get_tab_ddl.log　　prompt
　　prompt Creating function GET_TAB_DDL
　　prompt =============================
　　prompt
　　create or replace function get_tab_ddl(p_tab_name varchar2) return clob is
　　--返回表结构
　　v_result clob;
　　--表所在表空间
　　v_tablespace_name varchar2(200);
　　--表模式
　　v_logging varchar2(100);
　　--是否分区表
　　v_partitioned varchar2(100);
　　--前导空格
　　v_block varchar2(100) := '     ';
　　--区域ddl
　　v_ddl varchar2(32767);
　　--主键索引
　　v_pk_index varchar2(200);
　　begin
　　--表信息
　　select t.tablespace_name,
　　decode(t.logging, 'YES', 'LOGGING', 'NO LOGGING') logging,
　　t.partitioned
　　into v_tablespace_name,
　　v_logging,
　　v_partitioned
　　from user_tables t
　　where t.table_name = upper(p_tab_name);
　　v_result := '-- Create table' || chr(13) || 'create table ' ||
　　upper(p_tab_name) || '(' || chr(13);
　　--列信息
　　for col in (select '"' || c.COLUMN_NAME || '"' || ' ' || c.DATA_TYPE ||
　　decode(c.DATA_TYPE,
　　'VARCHAR2',
　　'(' || c.DATA_LENGTH || ') ',
　　'CHAR',
　　'(' || c.DATA_LENGTH || ') ',
　　decode(c.DATA_PRECISION,
　　null,
　　' ',
　　decode(c.DATA_SCALE,
　　0,
　　'(' || c.DATA_PRECISION || ') ',
　　'(' || c.DATA_PRECISION || ',' ||
　　c.DATA_SCALE || ') '))) ||
　　decode(c.NULLABLE, 'Y', 'NULL ', 'NOT NULL ') tab_col,
　　c.data_default
　　from user_tab_columns c
　　where c.table_name = upper(p_tab_name)
　　order by c.column_id)
　　loop
　　if col.data_default is null
　　then
　　v_result := v_result || v_block || col.tab_col || ',' || chr(13);
　　else
　　v_result := v_result || v_block || col.tab_col || 'DEFAULT ' ||
　　rtrim(col.data_default, chr(10)) || ',' || chr(13);
　　end if;
　　end loop;
　　v_result := rtrim(rtrim(v_result, chr(13)), ',') || chr(13) || ') ' ||
　　v_logging || chr(13) || 'tablespace ' || v_tablespace_name || ';' ||
　　chr(13);
　　-- Add comments to the table
　　select decode(m.comments,
　　null,
　　v_result,
　　v_result || '-- Add comments to the table' || chr(13) ||
　　'comment on table ' || m.table_name || ' is ''' || m.comments ||
　　''';' || chr(13))
　　into v_result
　　from user_tab_comments m
　　where m.table_name = upper(p_tab_name);
　　-- Add comments to the columns
　　v_ddl := '';
　　for com in (select 'comment on column ' || c.table_name || '.' ||
　　c.column_name || ' is ''' || c.comments || ''';' ||
　　chr(13) col_com
　　from user_col_comments c
　　where c.table_name = upper(p_tab_name)
　　and c.comments is not null)
　　loop
　　v_ddl := v_ddl || com.col_com;
　　end loop;
　　if v_ddl is not null
　　then
　　v_result := v_result || '-- Add comments to the columns ' || chr(13) ||
　　v_ddl;
　　end if;
　　-- Create/Recreate primary, unique and foreign key constraints
　　v_ddl            := '';
　　v_pk_index := '';
　　for con in (select c.constraint_name,
　　c.constraint_type,
　　c.search_condition,
　　c.r_constraint_name,
　　c.index_name,
　　decode(c.delete_rule, 'CASCADE', ' on delete cascade', '') delete_rule,
　　i.tablespace_name
　　from user_constraints c
　　left join user_indexes i on (c.index_name = i.index_name)
　　where c.table_name = upper(p_tab_name)
　　order by c.constraint_type)
　　loop
　　--pk
　　if con.constraint_type = 'P'
　　then
　　v_pk_index := con.index_name;
　　v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) ||
　　' add constraint ' || con.constraint_name ||
　　' primary key(';
　　for pk in (select c.column_name
　　from user_cons_columns c
　　where c.constraint_name = con.constraint_name
　　order by c.position)
　　loop
　　v_ddl := v_ddl || pk.column_name || ',';
　　end loop;
　　v_ddl := rtrim(v_ddl, ',') || ')' || chr(13) || 'using index' ||
　　chr(13) || 'tablespace ' || con.tablespace_name || ';' ||
　　chr(13);
　　end if;
　　--fk
　　if con.constraint_type = 'R'
　　then
　　v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) ||
　　' add constraint ' || con.constraint_name ||
　　' foreign key(';
　　for spk in (select c.column_name
　　from user_cons_columns c
　　where c.constraint_name = con.constraint_name
　　order by c.position)
　　loop
　　v_ddl := v_ddl || spk.column_name || ',';
　　end loop;
　　v_ddl := rtrim(v_ddl, ',') || ')';
　　select distinct (v_ddl || ' references ' || c.table_name || '(')
　　into v_ddl
　　from user_cons_columns c
　　where c.constraint_name = con.r_constraint_name;
　　for tfk in (select c.column_name
　　from user_cons_columns c
　　where c.constraint_name = con.r_constraint_name
　　order by c.position)
　　loop
　　v_ddl := v_ddl || tfk.column_name || ',';
　　end loop;
　　v_ddl := rtrim(v_ddl, ',') || ')' || con.delete_rule || ';' ||
　　chr(13);
　　end if;
　　--check
　　if (con.constraint_type = 'C' and
　　instr(con.search_condition, 'NOT NULL') = 0)
　　then
　　v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) ||
　　' add check (' || rtrim(con.search_condition, chr(10)) || ');' ||
　　chr(13);
　　end if;
　　end loop;
　　if v_ddl is null
　　then
　　v_result := v_result || chr(13);
　　else
　　v_result := v_result ||
　　'-- Create/Recreate primary, unique and foreign key constraints ' ||
　　chr(13) || v_ddl;
　　end if;
　　-- Create/Recreate indexes
　　v_ddl := '';

　　for>　　t.table_name,
　　decode(t.uniqueness,
　　'NONUNIQUE',
　　' ',
　　' ' || t.uniqueness || ' ') uniqueness,
　　t.tablespace_name
　　from user_indexes t
　　where t.table_name = upper(p_tab_name)
　　and t.index_type  'LOB'
　　and t.index_name  v_pk_index
　　and instr(t.index_name, 'SYS_C00')  1)
　　loop

　　v_ddl := v_ddl || 'create' ||>
　　>　　for i_col in (select c.column_name || ' ' || c.descend column_name
　　from user_ind_columns c

　　where c.index_name =>　　order by c.column_position)
　　loop
　　v_ddl := v_ddl || i_col.column_name || ',';
　　end loop;
　　v_ddl := rtrim(v_ddl, ',') || ')' || ' tablespace ' ||

　　>　　end loop;
　　if v_ddl is null
　　then
　　v_result := v_result || chr(13);
　　else
　　v_result := v_result || '-- Create/Recreate indexes' || chr(13) ||
　　v_ddl;
　　end if;
　　return(v_result);
　　end get_tab_ddl;
　　/
　　spool off