oracle ddl 主键,oracle-获得DDL-建表、注释、索引、主键

在工作中经常需要获得表的相关DDL，PL/SQL 和ORACLE 都有该功能，不过他们的功能太强大用起来不是很方便，自己就写了一个脚本。

希望该脚本可以对大家有所有帮助。所有的脚本我已经打包压缩上传到CSDN中

http://download.csdn.net/source/2895475

缺陷：字段最后都有","生成脚本后要手动剔除

把每个字段后都有“,”修改了 --2010.12.10

--01_create_temp_table.sql

建立临时表t_get_ddl_temp，存放想生成DDL的表名和属主

t_store_ddl_script 存放生成DDL的脚本

create table t_get_ddl_temp(

owner      varchar2(30),    --属主

table_name varchar2(30)     --表名

);

insert into t_get_ddl_temp(owner      ,table_name) values ('TEST','TEST1');

insert into t_get_ddl_temp(owner      ,table_name) values ('TEST','TEST2');

commit;

/*

---存放生成的DDL,

---object_type

-----1 1-1 create table

1-2 table column

1-3 end

-----2 2-1 table comments

-----3 3-1 column comments

-----4 4-1 alter primary key

4-2 key column

4-3 end

-----5 5-1 create index

5-2 index column

5-3 end

*/

create table t_store_ddl_script(

owner      varchar2(30),       --属主

table_name varchar2(60),       --表名

object_type varchar2(10),      --DDL 类型

script_id   int,               --语句顺序

ddl_script varchar2(4000)      --语句

);

--------------------------------------------------------------------------------------------------------------------------------------

----02_create_ddl.sql，生成DDL

declare

var_owner        varchar2(30);

var_table_name   varchar2(30);

var_index_name   varchar2(30);

var_max_col_id   int;

--get tables name which you want to get ddl

cursor cur_tab_name is

select

distinct

owner,

table_name

from t_get_ddl_temp

order by table_name asc;

---get  table's indexes

cursor cur_index_name is

select

index_name

from all_indexes ai

where ai.table_name = var_table_name

and ai.owner = var_owner    ;

begin

--open table's cursor

open cur_tab_name;

loop

fetch cur_tab_name into var_owner,var_table_name;

exit when cur_tab_name%notfound;

---create table

--------------------------

--create table start

--------------------------

----CREATE

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select   var_owner,

var_table_name,

'1-1',

1,

'Create table '||aat.owner||'.'||aat.table_name||'('

from all_all_tables aat

where table_name =var_table_name

and aat.owner = var_owner;

----max column id

select max( atc.COLUMN_ID) into  var_max_col_id

from all_tab_columns atc

where atc.TABLE_NAME =var_table_name

and atc.owner = var_owner;

----COLUMN

insert into t_store_ddl_script (owner,table_name,object_type,script_id,ddl_script)

select var_owner,var_table_name,

'1-2',

atc.COLUMN_ID,

'           '||rpad(atc.COLUMN_NAME,30,' ') ||

atc.DATA_TYPE ||

--判断字段类型

(case when INSTR(atc.DATA_TYPE,'CHAR')>0  then '('||atc.DATA_LENGTH||')'

when  INSTR(atc.DATA_TYPE,'NUMBER')>0 then '('||atc.DATA_PRECISION|| decode(atc.DATA_SCALE,0,'',','||atc.DATA_SCALE) ||''||')'

else

''

end)|| decode(atc.COLUMN_ID,var_max_col_id,'',',')

from all_tab_columns atc

where atc.TABLE_NAME =var_table_name

and atc.owner = var_owner

order by atc.COLUMN_ID;

----END

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select   var_owner,

var_table_name,

'1-3',

1,

');'  ddl_str from dual;

--------------------------

--create table END

--------------------------

--------------------------

--COMMENTS start

--------------------------

----TABLE COMMENTS 2-1

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select    var_owner,

var_table_name,

'2-1',

1,

'comment on table '||rpad(ATC.TABLE_NAME,30,' ') || ' is ' ||'''' ||atc.comments||''''||';' ddl_str

FROM ALL_TAB_COMMENTS ATC

WHERE ATC.TABLE_NAME =var_table_name

and atc.owner =var_owner ;

----CLOUMN COMMENTS 3-1

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select var_owner,var_table_name,

'3-1',

atco.COLUMN_ID,

'comment on column '||rpad(acc.table_name||'.'||acc.column_name,60,' ') ||' is '|| ''''||acc.comments||''''||';'

from all_col_comments acc,all_tab_columns atco

where acc.table_name = atco.table_name

and acc.column_name = atco.column_name

and acc.table_name = var_table_name

and atco.OWNER =var_owner

and atco.OWNER = acc.owner

order by atco.COLUMN_ID;

--------------------------

--COMMENTS end

--------------------------

--------------------------

--primary key start

--------------------------

----alter

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select var_owner,var_table_name,

'4-1',

1,

'alter table '||ac.table_name||' add constraint '||ac.constraint_name ||' primary key ('

from all_constraints ac,all_ind_columns aic

where  ac.table_name =var_table_name

and ac.constraint_type ='P'

and ac.table_name = aic.table_name

and ac.constraint_name = aic.index_name

and ac.OWNER =var_owner

and ac.OWNER = aic.index_owner

and rownum = 1;

-- max key column id

select

max(aic.column_position) into var_max_col_id

from all_constraints ac,all_ind_columns aic

where  ac.table_name =var_table_name

and ac.constraint_type ='P'

and ac.table_name = aic.table_name

and ac.constraint_name = aic.index_name

and ac.OWNER =var_owner

and ac.OWNER = aic.index_owner;

----key column

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select var_owner,var_table_name,

'4-2',

aic.column_position,

'           '||aic.column_name||decode(aic.column_position,var_max_col_id,'',',' )

from all_constraints ac,all_ind_columns aic

where  ac.table_name =var_table_name

and ac.constraint_type ='P'

and ac.table_name = aic.table_name

and ac.constraint_name = aic.index_name

and ac.OWNER =var_owner

and ac.OWNER = aic.index_owner

order by aic.column_position;

----end

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select var_owner,var_table_name,

'4-3',

1,

');'

from dual;

--------------------------

--primary key end

--------------------------

--open index's cursor

open cur_index_name;

loop

fetch cur_index_name into var_index_name;

exit when cur_index_name%notfound;

--------------------------

--index start

--------------------------

----create

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select var_owner,var_table_name||'-'||var_index_name,

'5-1',

1,

'create index '|| ai.index_name||' on '||ai.table_name||'(' ddl_str

from all_indexes ai,all_ind_columns aic

where ai.table_name =var_table_name

--and ai.uniqueness = 'NONUNIQUE'

and aic.table_name = ai.table_name

and aic.index_name = ai.index_name

and  ai.index_name =var_index_name

and ai.OWNER =var_owner

and ai.OWNER = aic.index_owner

and rownum = 1;

---max index column

select

max(aic.column_position) into var_max_col_id

from all_indexes ai,all_ind_columns aic

where ai.table_name =var_table_name

--and ai.uniqueness = 'NONUNIQUE'

and aic.table_name = ai.table_name

and aic.index_name = ai.index_name

and  ai.index_name =var_index_name

and ai.OWNER =var_owner

and ai.OWNER = aic.index_owner;

----index column

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select  var_owner,var_table_name||'-'||var_index_name,

'5-2',

aic.column_position,

'           '||aic.column_name||decode(aic.column_position,var_max_col_id,'',',' ) ddl_str

from all_indexes ai,all_ind_columns aic

where ai.table_name =var_table_name

--and ai.uniqueness = 'NONUNIQUE'

and aic.table_name = ai.table_name

and aic.index_name = ai.index_name

and  ai.index_name =var_index_name

and ai.OWNER =var_owner

and ai.OWNER = aic.index_owner

order by aic.column_position;

----end

insert into t_store_ddl_script (owner,table_name ,object_type,script_id,ddl_script)

select  var_owner,var_table_name||'-'||var_index_name,

'5-3',

1,

');' from dual;

--------------------------

--index end

--------------------------

end loop;

close cur_index_name;

end loop;

close cur_tab_name;

commit;

end;

/

--------------------------------------------------------------------------------------------------------

--03_create_ddl_file.sql 把生成的DDL，导出到文本中

set linesize 500 set feedback off set timing   off set term     off set heading  off set recsep   off set pagesize 0 set verify off set echo off spool d:/temp/ddl.sql SELECT tsds.ddl_script FROM t_store_ddl_script tsds order by tsds.owner,          tsds.table_name,          tsds.object_type,          tsds.script_id; spool off