RE: ALTER TABLE ADD CONSTRAINT generation script

From: Norwood Bradly A <Bradley.A.Norwood_at_M1.IRSCOUNSEL.TREAS.GOV>
Date: Tue, 2 May 2000 15:46:44 -0400
Message-Id: <10485.104730@fatcity.com>

Try this, Milos.
It's not mine...I probably picked it up at dbavillage.com last year.

---

---

REM crtkycns.sql checked out on 12/22/97 17:05:12 version 1.4 created on 8/27/97 13:11:40
set heading off
set verify off
set embedded on
set pagesize 0
set linesize 132
set arraysize 2
set space 0
set wrap off
set define '^'
column max_column_position new_value max_position noprint; column sum_bytes new_value tot_bytes noprint;

select max(position) max_column_position from dba_cons_columns
where table_name = upper('^2')
  and constraint_name = upper('^3')
  and owner = upper('^1');

select nvl(sum(bytes),0)/1024 sum_bytes
from dba_extents
where segment_name = upper('^3')
  and owner = upper('^1');

set termout on
prompt REM Generating create SQL for: ^1..^2 key constraint: ^3 compressed extents: ^4
set termout off
select
initcap('prompt Creating'||decode(constraint_type,'P',' PRIMARY KEY ','U',' UNIQUE ',' ')||'constraint on Table: ')||upper('^2')||' Constraint:
'||'^3' buff,
'ALTER TABLE '||upper('^2')||' ADD CONSTRAINT
'||upper('^3')||decode(constraint_type,'P',' PRIMARY KEY ','U',' UNIQUE ','
')||'('

 from dba_constraints
where constraint_name = upper('^3')
  and table_name = upper('^2')
  and owner = upper('^1');
select
' '||rpad(column_name,35,' ')||decode(position,^max_position,')',',')
from dba_cons_columns
where table_name = upper('^2')
  and constraint_name = upper('^3')
  and owner = upper('^1')
order by position;
select ';' buff,
'REM Warning! Constraint ^1..^3 has an index with another name to enforce
uniqueness!' buff
from dual
where not exists (select index_name
from dba_indexes dbi,

     dba_constraints dbc

where dbi.table_name = upper('^2')
  and dbc.constraint_name = upper('^3')
  and dbi.index_name = upper('^3')
  and dbi.table_owner = upper('^1')
  and dbc.owner = upper('^1')
  and dbc.table_name = upper('^2'));

select
'USING INDEX ' buff,

decode(NVL(pct_free,-1),-1,'','       PCTFREE '||pct_free) buff,
decode(NVL(ini_trans,-1),-1,'','      INITRANS '||ini_trans) buff,
decode(NVL(max_trans,-1),-1,'','      MAXTRANS '||max_trans) buff,


'      TABLESPACE '||dbi.tablespace_name buff,

'STORAGE (' buff,

'         INITIAL '||decode('^4','Y',^tot_bytes||' K','y',^tot_bytes||'

K',initial_extent/1024||' K') buff,
decode(NVL(next_extent,-1),-1,'','          NEXT '||next_extent) buff,
decode(NVL(next_extent,-1),-1,'','          PCTINCREASE '||pct_increase)
buff,
decode(NVL(min_extents,-1),-1,'','          MINEXTENTS '||min_extents) buff,
decode(NVL(max_extents,-1),-1,'','          MAXEXTENTS '||max_extents) buff,


'        )'||decode(dbc.status,'DISABLED',' DISABLE;','ENABLED',';') buff

from dba_indexes dbi,
     dba_constraints dbc
where dbi.table_name = upper('^2')
  and dbc.constraint_name = upper('^3')
  and dbi.index_name = upper('^3')
  and dbi.table_owner = upper('^1')

  and dbc.owner = upper('^1')
  and dbc.table_name = upper('^2');

-----Original Message-----
From: Milos.Martinovic_at_slovnaft.sk [mailto:Milos.Martinovic_at_slovnaft.sk] Sent: Tuesday, May 02, 2000 2:31 PM
To: Multiple recipients of list ORACLE-L Subject: ALTER TABLE ADD CONSTRAINT generation script

Hi all,

does anybody have the script for generating a series of "ALTER TABLE tbl ADD CONSTRAINT ..." statements from DBA_CONSTRAINTS a DBA_CONS_COLUMNS ?

I need one and as I found out it is not that trivial (due to multiple possible entries in dba_cons_columns for every constraint)

Thanx in advance

Milos Martinovic
DBA

-- 
Author: 
  INET: Milos.Martinovic_at_slovnaft.sk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Tue May 02 2000 - 14:46:44 CDT