set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* script to spool a listing of all constraint      */
/*    statements required to rebuild table.         */
/* Contents: Table name, col definitions and        */
/*    storage parameters.                           */
/*                                                  */
/*         @c:\oracle\sql\bld_con2.sql              */
/*                                                  */ 
/*       >> For a specific table name <<            */
/*         >> Only check contraints <<              */ 
/*                                                  */
/* ************************************************ */

/* set termout off; */
set echo off;
set feedback off;
set long 50;

col dummy 	noprint format a1;
col dummy2 	noprint format a1;
col table_name 	noprint format a1;
col con 	noprint format a1;
/* col command 	format a70; */

/* ********************************* */
/* 1. Generate alter table commands  */
/* ********************************* */

/* select 'alter table '||A.owner||'.'||A.table_name||' ADD CONSTRAINT '||A.CONSTRAINT_NAME||' CHECK ('
   from all_constraints	A
   where A.owner NOT in ('SYS', 'SYSTEM')               and
         A.CONSTRAINT_TYPE 	= 'C'                   and
         A.table_name 		= upper('&&TABLE_NAME') and
	 A.CONSTRAINT_NAME  not like 'SYS%'             and	/* Exclude NOT NULL */
	 A.SEARCH_CONDITION is NOT NULL
UNION
select to_char(A.SEARCH_CONDITION)
   from all_constraints	A
   where A.owner NOT in ('SYS', 'SYSTEM')               and
         A.CONSTRAINT_TYPE 	= 'C'                   and
         A.table_name 		= upper('&&TABLE_NAME') and
	 A.CONSTRAINT_NAME  not like 'SYS%'             and	/* Exclude NOT NULL */
	 A.SEARCH_CONDITION is NOT NULL; */

select substr(to_char(A.SEARCH_CONDITION),1,100)
   from all_constraints	A
   where A.owner NOT in ('SYS', 'SYSTEM')               and
         A.CONSTRAINT_TYPE 	= 'C'                   and
         A.table_name 		= upper('&&TABLE_NAME') and
	 A.CONSTRAINT_NAME  not like 'SYS%';

/* undefine TABLE_NAME; */
set heading on;
set pagesize 100;
set termout on;

commit;










