Consulta constraints da tabela

[tfg]

Quero consultar as constraints de uma tabela... Como posso fazer isso?

[dr_gori]

Pode usar o script abaixo:

Selecionar tudo

SET show off feedback off ver off echo off
accept vowner prompt  "Cols de Constraints do Owner  (Enter=All) : "
accept vtabela prompt "Cols de Constraints da tabela             : " DEF %

column column_name format a25;
SELECT   ac.constraint_name
     ,   acc.column_name
     ,   acc.position
     ,   ac.search_condition
     ,   ac.r_constraint_name
     ,   ac.constraint_type
     ,   ac.owner
FROM     all_cons_columns acc
     ,   all_constraints  ac
WHERE    ac.owner             = NVL(UPPER('&vowner'), ac.owner)
  AND    ac.table_name        = upper('&vtabela')
  AND    ac.constraint_type   <> 'R'
  AND    acc.owner            = ac.owner
  AND    acc.table_name       = ac.table_name
  AND    acc.constraint_name  = ac.constraint_name
ORDER BY DECODE( ac.constraint_type,'P','A',ac.constraint_type)
       , ac.constraint_name
       , acc.position
/
set head off
SELECT   ac.constraint_name
     ,   acc.column_name
     ,   acc.position
     ,   acr.owner||'.'||acr.table_name
     ,   ac.r_constraint_name
     ,   ac.constraint_type
     ,   ac.owner
FROM     all_constraints acr
     ,   all_cons_columns acc
     ,   all_constraints  ac
WHERE    ac.owner             = NVL(UPPER('&vowner'), ac.owner)
  AND    ac.table_name        = upper('&vtabela')
  AND    ac.constraint_type   = 'R'
  AND    acc.owner            = ac.owner
  AND    acc.constraint_name  = ac.constraint_name
  AND    acc.table_name       = ac.table_name
  AND    acr.owner            = ac.r_owner
  AND    acr.constraint_name  = ac.r_constraint_name
ORDER BY ac.constraint_name
       , acc.position
/
set head on
set feedback on 
set ver on 

Foi testado nos seguintes bancos:
Oracle 7 (7.3.4)
Oracle 8 (8..0.6)
Oracle 8i (8.1.7)

[dr_gori]

Esse é outro que eu estou usando ultimamente:

Selecionar tudo

SET show off feedback off ver off echo off
SET PAGESIZE 50
accept vtabela prompt "Colunas de Constraints da tabela :  "

break on constraint_name
column column_name format a30;
column pos format a3;
select DECODE(A.POSITION, 1,  a.constraint_name ||' ('||C.TABLE_NAME||')'  , NULL) CONST, 
       to_char(a.position) pos,
       a.column_name, 
       b.search_condition
from user_cons_columns a, user_constraints b
, USER_CONSTRAINTS C
where b.table_name=upper('&vtabela')
  and a.owner=b.owner
  and a.table_name=b.table_name
  and a.constraint_name=b.constraint_name
  AND B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME(+)
order by decode(b.constraint_type,'P','A',b.constraint_type),
         b.constraint_name, 
         a.position
/
clear breaks
column pos clear
set feedback on 
set ver on 
SET PAGESIZE 20

[Hahu]

Fala dr_gori,
Boa tarde,
Amigo..para que serve o type_constraints, o que significa?
Abs de seu amigo Hahu

[Trevisolli]

Brother, pelo q li, significa o tipo de constraint...
Como:

Selecionar tudo

P - PRIMARY KEY 
R - FOREIGN KEY 
U - UNIQUE 
C - CHECK 

qualquer coisa, manda ai.

[Hahu]

Valeu trevisolli, eu tinha me atrabalhado na letra "R"...porque não saiba se era foreign mesmo!! abs de seu amigo Hahu