--------------------------
--
-- by,Marcos de Meira Gois
--
---------------------------
set linesize 2000
set pagesize 2000
set verify off

col column_name for a70

accept Tabela       char prompt "Nome da tabela: "

PROMPT Chaves Primárias

select Substr(t.constraint_name,1,20) Constraint
      ,Substr(t.status         ,1,06) Status
      ,Substr(t.table_name     ,1,20) Tabela
      ,Substr(column_name      ,1,20) Coluna
    from user_constraints  t
        ,user_cons_columns c
    where constraint_type   = 'P' 
      and t.constraint_name = c.constraint_name
      and t.table_name      = UPPER('&Tabela')
    order by t.table_name
            ,c.constraint_name
/
PROMPT Chaves Estrangeiras
 select Substr(t.constraint_name,1,20) Constraint
       ,Substr(t.status         ,1,06) Status
       ,Rpad(Substr(lower(c.column_name),1,25),25, ' ')||' ==>> ' Referencia
       ,Substr(r.table_name,1,20)||'.'||Substr(lower(cc.column_name),1,25) Referenciada
     from user_constraints  t
         ,user_cons_columns c
         ,user_cons_columns cc
         ,user_constraints  r
     where t.constraint_type   = 'R' 
       and t.table_name        = c.table_name 
       and t.constraint_name   = c.constraint_name 
       and t.r_constraint_name = r.constraint_name 
       and r.constraint_name   = cc.constraint_name 
       and t.table_name        = UPPER('&Tabela')
       and c.position          = cc.position
/
PROMPT Indices
  Select uc.index_name
        ,uc.column_name
        ,ui.uniqueness 
        ,ui.status
    from user_ind_columns uc
        ,user_indexes     ui
   Where UPPER(uc.table_name) = UPPER('&Tabela')
     And uc.table_name        = ui.table_name
     And uc.index_name        = ui.index_name
/
PROMPT Chaves Estrangeiras
accept vConstraint  char prompt "Nome da Constraint: "
 select Substr(t.constraint_name,1,20) Constraint
       ,Substr(t.table_name,1,20) Tabela
     from user_constraints  t
     where t.Constraint_name = UPPER('&vConstraint')
/
Prompt
Prompt Lista TODAS as relações da tabela com as demais do sistema
Prompt
select Substr(Rpad(t.table_name,20,' '),1,20) Tabela,
       Substr(Rpad(t.constraint_name,20,' '),1,20) Constraint,
       t.status 
  from user_constraints  t
 where t.constraint_type     = 'R' 
   and t.r_constraint_name   = 
        (select Distinct t.constraint_name Pk
           from user_constraints  t
               ,user_cons_columns c
          where constraint_type   = 'P' 
            and t.constraint_name = c.constraint_name
            and t.table_name      = UPPER('&Tabela'))
/