Oracle User constraints info insert into a table

 

SQL> CREATE TABLE cons_def (ID NUMBER,table_name VARCHAR2(30),DDL VARCHAR2(4000));

Table created.

SQL> CREATE SEQUENCE seq_cons START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE NOMAXVALUE;

Sequence created.




DECLARE
   my_stmt   VARCHAR2 (4000);
BEGIN
   FOR x IN (SELECT   UPPER (table_name) NAME
             FROM user_tables
             ORDER BY 1)
   LOOP
      my_stmt := NULL;

for y in (select  'alter table "' || child_tname || '"' || chr(10) ||
                            'add constraint "' || child_cons_name || '"' || 
chr(10) ||'foreign key ( ' || child_columns || ' ) ' || 
chr(10) || 'references "' || parent_tname || '" ( ' || parent_columns || ') '|| decode(DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') fkey ,
 x.name
from ( select a.table_name child_tname, a.constraint_name child_cons_name,
                            b.r_constraint_name parent_cons_name, b.deferred,
                                 max(decode(position, 1,     '"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 2,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 3,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 4,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 5,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 6,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 7,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 8,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 9,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,10,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,11,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,12,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,13,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,14,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,15,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,16,', '||'"'||
                                substr(column_name,1,30)||'"',NULL))  child_columns
                           from user_cons_columns a, user_constraints b
                           where a.constraint_name = b.constraint_name
                           and b.constraint_type = 'R'
             group by a.table_name, a.constraint_name,b.r_constraint_name,b.deferred ) child,
            ( select a.constraint_name parent_cons_name,a.table_name parent_tname,
                                 max(decode(position, 1,     '"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 2,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 3,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 4,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 5,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 6,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 7,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 8,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position, 9,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,10,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,11,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,12,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,13,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,14,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,15,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL)) ||
                                 max(decode(position,16,', '||'"'||
                                        substr(column_name,1,30)||'"',NULL))
                                    parent_columns
                            from user_cons_columns a, user_constraints b
                           where a.constraint_name = b.constraint_name
                             and b.constraint_type in ( 'P', 'U' )
                           group by a.table_name, a.constraint_name ) parent
                      where child.parent_cons_name = parent.parent_cons_name
                             and parent.parent_tname = x.name) 
                             loop

INSERT INTO cons_def
            (ID,
             table_name,
             DDL
            )
     VALUES (seq_cons.NEXTVAL,
             y.NAME,
             y.fkey
            );
        end loop;
   END LOOP;
   
    commit;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Error in creating DDL: ' || SQLERRM);
      RAISE;
END;
/






SQL> select * from cons_def;

        ID TABLE_NAME
---------- ------------------------------
DDL
------------------------------------------------------------------------
         1 ANUJ1
alter table "ANUJ2"
add constraint "ANUJ2_FK"
foreign key ( "N" )
references "ANUJ1" ( "X")

         2 DAM_PARAM_TAB$
alter table "DAM_CONFIG_PARAM$"
add constraint "DAM_CONFIG_PARAM_FK1"
foreign key ( "PARAM_ID" )
references "DAM_PARAM_TAB$" ( "PARAMETER#")