Consulta constraints da tabela
- dr_gori
- Moderador
- Mensagens: 5024
- Registrado em: Seg, 03 Mai 2004 3:08 pm
- Localização: Portland, OR USA
- Contato:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Pode usar o script abaixo:
Foi testado nos seguintes bancos:
Oracle 7 (7.3.4)
Oracle 8 (8..0.6)
Oracle 8i (8.1.7)
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
Oracle 7 (7.3.4)
Oracle 8 (8..0.6)
Oracle 8i (8.1.7)
- dr_gori
- Moderador
- Mensagens: 5024
- Registrado em: Seg, 03 Mai 2004 3:08 pm
- Localização: Portland, OR USA
- Contato:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Esse é outro que eu estou usando ultimamente:
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
-
- Moderador
- Mensagens: 2016
- Registrado em: Qua, 12 Jan 2005 3:25 pm
- Localização: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother, pelo q li, significa o tipo de constraint...
Como:
qualquer coisa, manda ai.
Como:
P - PRIMARY KEY
R - FOREIGN KEY
U - UNIQUE
C - CHECK
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 14 visitantes