Constraints

[Anônimo]

Olá CVPRADO,
Veja se esses 2 exemplos podem te ajudar.

SELECT t1.owner,
t1.constraint_name,
t2.column_name,
t1.table_name,
t1.status,
t1.validated,
DECODE(constraint_type,
‘V’,’Check Option on a view’,
‘R’,’Referential AKA Foreign Key’,
‘U’,’Unique Key’,
‘P’,’Primary Key’,
‘C’,’Check on a table’,
‘O’,’Read Only on a view’,
‘?’,’?’) AS “CONSTRAINT_TYPE”
FROM dba_constraints t1, dba_cons_columns t2
WHERE t1.owner IN (
–Neste Select defino que quero procurar todas as constraints que estejam nos schema que não estão no tablespace SYSTEM e SYSAUX.
SELECT username
FROM dba_users
WHERE default_tablespace NOT IN (‘SYSTEM’,’SYSAUX’)
)
ORDER BY t1.owner;

–Neste Select procuro pelo nome da constraint e o seu tipo.
SELECT t1.owner,
t1.constraint_name,
t2.column_name,
t1.table_name,
t1.status,
t1.validated,
DECODE(constraint_type,
‘V’,’Check Option on a view’,
‘R’,’Referential AKA Foreign Key’,
‘U’,’Unique Key’,
‘P’,’Primary Key’,
‘C’,’Check on a table’,
‘O’,’Read Only on a view’,
‘?’,’?’) AS “CONSTRAINT_TYPE”
FROM dba_constraints t1, dba_cons_columns t2
WHERE t1.constraint_name =’Nome_da_Constraint’
AND t1.constraint_type = ‘P’;
— Defina o tipo da constraint conforme no DECODE somente com a letra.

Abraços