Menu de navegação

[SusuParticipante]

Pessoal,
vcs conhecem alguma consulta que encontre o Pai e os filhos de uma tabela?

Eu conheco dba_constraints ele só encontra os filhos de uma tabela. Agora eu gostaria descobrir o Pai.

Muito obrigada,
Suzana C. da Costa

[Regis AraujoParticipante]

Ola Susu.. bom dia..!!

Poh.. vou ter que lhe entregar uma das minhas preciosidades…. mas fazer oq.. coloca o script abaixo em um arquivo .sql e chama ele pelo SQLPLUS.. acho que é isto que vc precisa..!!!

Ahhhh.. por esta informação são 100 reais.. =D


SET show off feedback off ver off echo off
SET PAGESIZE 80
COL TIPO FORMAT A10
COL OWNER FORMAT A10
COL R_CONSTRAINT_NAME FORMAT A40
COL TABELA FORMAT A30
COL COLUMNS FORMAT A30
COL STATUS FORMAT A10
accept TABELA prompt "TABLE NAME : "
break on tipo
SELECT
'PAI' TIPO
, A.OWNER
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.OWNER) TABELA
, B.COLUMNS
, A.R_CONSTRAINT_NAME
, B.STATUS
FROM ALL_CONSTRAINTS A
, ( SELECT
A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME
, B.STATUS
, MAX(DECODE(POSITION, 1, CNAME,NULL)) ||
MAX(DECODE(POSITION, 2,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 3,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 4,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 5,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 6,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 7,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 8,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 9,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,10,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,11,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,12,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,13,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,14,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,15,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,16,', '||CNAME,NULL)) COLUMNS
FROM (SELECT OWNER,
SUBSTR(COLUMN_NAME,1,30) CNAME,
SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
POSITION
FROM ALL_CONS_COLUMNS) A,
ALL_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME, B.STATUS ) B
WHERE A.TABLE_NAME = upper('&TABELA')
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_TYPE ='R'
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
UNION
SELECT
'FILHO' TIPO
, A.OWNER
, A.TABLE_NAME TABELA
, B.COLUMNS
, A.CONSTRAINT_NAME
, A.STATUS
FROM ALL_CONSTRAINTS A
, ( SELECT
A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME
, B.STATUS
, MAX(DECODE(POSITION, 1, CNAME,NULL)) ||
MAX(DECODE(POSITION, 2,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 3,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 4,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 5,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 6,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 7,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 8,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION, 9,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,10,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,11,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,12,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,13,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,14,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,15,', '||CNAME,NULL)) ||
MAX(DECODE(POSITION,16,', '||CNAME,NULL)) COLUMNS
FROM (SELECT OWNER,
SUBSTR(COLUMN_NAME,1,30) CNAME,
SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
POSITION
FROM ALL_CONS_COLUMNS) A,
ALL_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME, B.STATUS ) B
WHERE A.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = upper('&&TABELA')
AND CONSTRAINT_TYPE ='P')
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
ORDER BY TIPO DESC, TABELA, COLUMNS
/
clear breaks
set feedback on
set ver on
SET PAGESIZE 20


Tenho certeza que irá lhe ser muito util..!!

Abraços..!

[Autor]

Posts