Home -> Mailing Lists -> Oracle-L -> RES: table referenced by

RES: table referenced by

From: Rodrigo Mufalani <rodrigo_at_mufalani.com.br>
Date: Sat, 28 Apr 2012 16:29:36 -0300
Message-ID: <005101cd2575$4000ad80$c0020880$_at_mufalani.com.br> 





Hi Jo,



  I download this script from Tom kyte's blog and adjust to use DBA_XXXX
views instead USER_XXXXX views at


http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:863253291
155



    
  
  •   generating ddl from FK

    
SET TIMING OFF

SET TIME OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET TRIMSPOOL ON

SET PAGES 0

column fkey format a80 word_wrapped






ACCEPT SCHEMA PROMPT 'Enter with owner: '
ACCEPT tabela PROMPT 'Enter with table_name: '



SPOOL drop_fk1.SQL


select


'alter table '||child_owner||'."'|| child_tname || '" drop constraint "'||
child_cons_name || '";' fkey


from


( select  a.owner child_owner , a.table_name child_tname, a.constraint_name

child_cons_name,

         b.r_constraint_name parent_cons_name,
         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 dba_cons_columns a, dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'


   group by  a.owner, a.table_name, a.constraint_name, b.r_constraint_name )
child,


( select a.owner parent_owner, 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 dba_cons_columns a, dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P', 'U' )
   group by a.owner, a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
  and parent.parent_tname = upper('&TABELA')
  and parent.parent_owner= upper('&SCHEMA') ;  
SPOOL OFF



SPOOL create_fk1.SQL


select


'alter table '|| child_owner ||'."'|| child_tname || '"  add constraint "'||
child_cons_name || '" foreign key ( ' || child_columns || ' )  references
'|| parent_owner ||'."'|| parent_tname || '" ( ' || parent_columns || ');'
CR_fkey


from


( select  a.owner child_owner , a.table_name child_tname, a.constraint_name

child_cons_name,

         b.r_constraint_name parent_cons_name,
         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 dba_cons_columns a, dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'


   group by  a.owner, a.table_name, a.constraint_name, b.r_constraint_name )
child,


( select a.owner parent_owner, 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 dba_cons_columns a, dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P', 'U' )
   group by a.owner, a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
  and parent.parent_tname = upper('&TABELA')
  and parent.parent_owner= upper('&SCHEMA') ;  
SPOOL OFF;


--ED drop_fk1.SQL


--ED create_fk1.SQL



Best Regards,



Rodrigo Mufalani


Cel.: +55 21 88994817


rodrigo_at_mufalani.com.br


www.mufalani.com.br


twitter: _at_mufalani
  



-----Mensagem original-----


De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Em
nome de jo


Enviada em: sábado, 28 de abril de 2012 16:05
Para: ORACLE-L


Assunto: table referenced by



Hi all,



I'm trying to create a query to know which tables are linked with table
'anagrafica'


in PostgreSQL I can achieve it with this query:




SELECT 'anagrafica' AS table,


conrelid::pg_catalog.regclass AS referenced_by,
conname AS foreignkey_name


FROM pg_catalog.pg_constraint c


WHERE c.contype = 'f'


AND c.confrelid = (

     SELECT c.oid FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     WHERE c.relname = 'anagrafica' AND 


pg_catalog.pg_table_is_visible(c.oid)


    )





   table    |      referenced_by       |                  foreignkey_name
------------+--------------------------+------------------------------------
----------------
 anagrafica | asl                      | asl_id_anagrafica_fkey
 anagrafica | azienda                  | azienda_id_anagrafica_fkey






Is it possible to do do the same thing in Oracle?



j


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Received on Sat Apr 28 2012 - 14:29:36 CDT












Original text of this message