未测试表的外键生成脚本-优快云博客

Script
rem rem Description This SQL script generates referential constraints rem for a table. (Not including primary key info) rem rem Argument(s) Spool File Name, Table name rem SPOOL &spool..ref set PAGESIZE 0 set FEEDBACK OFF set ECHO OFF set VERIFY OFF set LINESIZE 132 COLUMN DUMMY_1 NOPRINT FORMAT A30 COLUMN DUMMY_2 NOPRINT FORMAT 9 COLUMN DUMMY_3 NOPRINT FORMAT 99 COLUMN COMMAND FORMAT A80 SELECT 'ALTER TABLE '\|\|C.TABLE_NAME\|\|' ADD CONSTRAINT '\|\| C.CONSTRAINT_NAME\|\|' FOREIGN KEY (' COMMAND, C.CONSTRAINT_NAME DUMMY_1, 1 DUMMY_2, 0 DUMMY_3 FROM SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') AND C.R_CONSTRAINT_NAME = I.INDEX_NAME AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R') UNION SELECT DECODE(CC.POSITION,1,NULL,',') \|\| CC.COLUMN_NAME COMMAND, CC.CONSTRAINT_NAME DUMMY_1, 2 DUMMY_2, CC.POSITION DUMMY_3 FROM SYS.DBA_CONS_COLUMNS CC,SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') AND C.R_CONSTRAINT_NAME = I.INDEX_NAME AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R') UNION SELECT ') REFERENCES '\|\|I.TABLE_NAME\|\|' (' COMMAND, C.CONSTRAINT_NAME DUMMY_1, 3 DUMMY_2, 0 DUMMY_3 FROM SYS.DBA_INDEXES I, SYS.DBA_CONSTRAINTS C WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') AND C.R_CONSTRAINT_NAME = I.INDEX_NAME AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R') UNION SELECT DECODE(IC.COLUMN_POSITION,1,NULL,',') \|\| IC.COLUMN_NAME COMMAND, C.CONSTRAINT_NAME DUMMY_1, 4 DUMMY_2, IC.COLUMN_POSITION DUMMY_3 FROM SYS.DBA_IND_COLUMNS IC, SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') AND C.R_CONSTRAINT_NAME = I.INDEX_NAME AND IC.INDEX_NAME = I.INDEX_NAME AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R') UNION SELECT ')'\|\|DECODE(C.DELETE_RULE,'CASCADE', ' ON DELETE CASCADE',NULL)\|\| DECODE(C.STATUS,'DISABLED',' DISABLE',NULL)\|\|';' COMMAND, C.CONSTRAINT_NAME DUMMY_1 ,5 DUMMY_2, 0 DUMMY_3 FROM SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') AND C.R_CONSTRAINT_NAME = I.INDEX_NAME AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R') ORDER BY 2,3,4; spool off exit

Script

rem 
rem Description   This SQL script generates referential constraints 
rem               for a table.  (Not including primary key info) 
rem 
rem Argument(s)   Spool File Name, Table name 
rem 

SPOOL &spool..ref 
  
set PAGESIZE 0 
set FEEDBACK OFF 
set ECHO OFF 
set VERIFY OFF 
set LINESIZE 132 
COLUMN DUMMY_1 NOPRINT FORMAT A30 
COLUMN DUMMY_2 NOPRINT FORMAT 9 
COLUMN DUMMY_3 NOPRINT FORMAT 99 
COLUMN COMMAND FORMAT A80 
  
 
SELECT 'ALTER TABLE '||C.TABLE_NAME||' ADD CONSTRAINT '|| 
       C.CONSTRAINT_NAME||' FOREIGN KEY (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 1 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(CC.POSITION,1,NULL,',') || CC.COLUMN_NAME COMMAND, 
       CC.CONSTRAINT_NAME DUMMY_1, 2 DUMMY_2, CC.POSITION DUMMY_3 
  FROM SYS.DBA_CONS_COLUMNS CC,SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
       AND CC.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ') REFERENCES '||I.TABLE_NAME||' (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 3 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_INDEXES I, SYS.DBA_CONSTRAINTS C 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(IC.COLUMN_POSITION,1,NULL,',') ||  
       IC.COLUMN_NAME COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 4 DUMMY_2, 
       IC.COLUMN_POSITION DUMMY_3 
  FROM SYS.DBA_IND_COLUMNS IC,  
       SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND IC.INDEX_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ')'||DECODE(C.DELETE_RULE,'CASCADE', 
       ' ON DELETE CASCADE',NULL)|| 
       DECODE(C.STATUS,'DISABLED',' DISABLE',NULL)||';' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1 ,5 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       ORDER BY 2,3,4; 

spool off 
exit