java有外键怎么导出_导出当前用户下所有外键、主键、索引、sequence的DDL

在数据转换的过程中，我使用create table as ...等方式，批量将A的数据表创建到B下后，

主键、外键、索引是需要手工建的，一个一个嫌麻烦，故写下了以下脚本。

可使A用户下的主键、外键、索引都产生出来，然后到B用户下创建。

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

10g及以上

--导出索引

SELECT 'CREATE ' || DECODE(B.INDEX_TYPE, 'NORMAL', '', B.INDEX_TYPE) ||

DECODE(B.UNIQUENESS, 'NONUNIQUE', '', B.UNIQUENESS) || ' INDEX ' ||

B.INDEX_NAME || ' ON ' || B.TABLE_NAME || ' (' ||

(SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))

FROM USER_IND_COLUMNS A

WHERE A.INDEX_NAME = B.INDEX_NAME) || ');'

FROM USER_INDEXES B

WHERE B.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05');

--导出主键

SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||

B.CONSTRAINT_NAME || ' PRIMARY KEY (' ||

(SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))

FROM USER_CONS_COLUMNS A

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ');'

FROM USER_CONSTRAINTS B

WHERE B.CONSTRAINT_TYPE = 'P'

AND B.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05');

--导出外键

SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||

B.CONSTRAINT_NAME || ' FOREIGN KEY (' ||

(SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))

FROM USER_CONS_COLUMNS A

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ') REFERENCES ' ||

(SELECT B1.table_name FROM USER_CONSTRAINTS B1

WHERE B1.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || '(' ||

(SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))

FROM USER_CONS_COLUMNS A

WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || ');'

FROM USER_CONSTRAINTS B

WHERE B.CONSTRAINT_TYPE = 'R'

AND B.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05');

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

9i、10g都可以

SELECT 'drop SEQUENCE ' || T.SEQUENCE_NAME || ';'

FROM USER_SEQUENCES T

WHERE T.SEQUENCE_NAME IN

('SEQ_A_AAZ257', 'SEQ_A_BAZ601', 'SEQ_A_BAE088', 'SEQ_A_AAZ033',

'SEQ_A_BAZ605', 'SEQ_A_BAZ603', 'SEQ_A_BAZ001', 'SEQ_A_BAZ002',

'SEQ_A_BAZ601', 'SEQ_A_BAZ612', 'SEQ_A_BAZ613', 'SEQ_A_BAZ606',

'SEQ_A_BAZ609', 'SEQ_A_BAC402', 'SEQ_A_BZA203', 'SEQ_A_BZA668',

'SEQ_A_BAZ607');

--导出sequence

SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE ||

' MAXVALUE ' || MAX_VALUE || ' START WITH ' || LAST_NUMBER ||

' INCREMENT BY ' || INCREMENT_BY || (CASE

WHEN CACHE_SIZE = 0 THEN

' NOCACHE'

ELSE

' CACHE ' || CACHE_SIZE

END) || ';'

FROM USER_SEQUENCES;

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

9i及以下

CREATE OR REPLACE FUNCTION ZJW_C2L(IND IN VARCHAR2) RETURN VARCHAR2 IS

RET_MSG VARCHAR2(1000);

BEGIN

SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')), 2) AS COLUMN_NAME

INTO RET_MSG

FROM (SELECT C.COLUMN_NAME, ROWNUM AS RN

FROM USER_IND_COLUMNS C

WHERE C.INDEX_NAME = IND

ORDER BY C.COLUMN_POSITION)

START WITH RN = 1

CONNECT BY RN - 1 = PRIOR RN;

RETURN RET_MSG;

END ZJW_C2L;

--导出索引

SELECT 'CREATE ' || DECODE(T.INDEX_TYPE, 'NORMAL', '', T.INDEX_TYPE) ||

DECODE(T.UNIQUENESS, 'NONUNIQUE', '', T.UNIQUENESS) || ' INDEX ' ||

T.INDEX_NAME || ' ON ' || T.TABLE_NAME || ' (' ||

(ZJW_C2L(T.INDEX_NAME)) || ');'

FROM USER_INDEXES T

WHERE T.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05')

--导出主键

SELECT 'alter table '||T.table_name||

' add constraint '||T.constraint_name||' primary key ('||ZJW_C2L(T.constraint_name)||');'

FROM USER_CONSTRAINTS T

WHERE T.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05')

AND T.CONSTRAINT_TYPE ='P';

--导出外键

SELECT 'alter table '||T.table_name||

' add constraint '||T.constraint_name||' foreign key ('||ZJW_C2L(T.R_constraint_name)||

') references '||(SELECT Q.TABLE_NAME FROM USER_CONSTRAINTS Q WHERE Q.constraint_name = T.r_constraint_name) ||'('||ZJW_C2L(T.r_constraint_name)||');'

FROM USER_CONSTRAINTS T

WHERE T.TABLE_NAME IN

('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',

'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',

'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',

'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',

'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',

'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',

'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',

'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',

'MC01', 'MC01', 'MC02', 'MC05')

AND T.CONSTRAINT_TYPE ='R';

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

也可以通过DBMS_METADATA.GET_DDL来取得对象DDL

-----SELECT DBMS_METADATA.GET_DDL(A.OBJECT_TYPE,A.OBJECT_NAME) from dba_objects where object_type='TABLE';

object_type:

CLUSTER

CONSUMER GROUP

CONTEXT

DATABASE LINK

DIMENSION

DIRECTORY

EVALUATION CONTEXT

FUNCTION

INDEX

INDEX PARTITION

INDEXTYPE

JAVA CLASS

JAVA DATA

JAVA RESOURCE

JAVA SOURCE

LIBRARY

LOB

MATERIALIZED VIEW

OPERATOR

PACKAGE

PACKAGE BODY

PROCEDURE

QUEUE

RESOURCE PLAN

RULE SET

SEQUENCE

SYNONYM

TABLE

TABLE PARTITION

TRIGGER

TYPE

TYPE BODY

VIEW

XML SCHEMA

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29968458/viewspace-1361515/，如需转载，请注明出处，否则将追究法律责任。