O script abaixo mostra os relacionamentos e as colunas de uma determinada tabela:
-- ------------------------------------------------- -- cons.sql
-- -------------------------------------------------
-- Mostra quais são as constraints PAIS e FILHOS de
-- uma determinada tabela. Também mostra os campos
-- dela lado a lado para consulta.
-- -------------------------------------------------
-- Shows what constraints has relationship with the
-- informed table. Also shows the columns of these
-- relation constraints. (Pai-> father, Filho-> Son)
-- -------------------------------------------------
-- Created by Thomas F. Glufke
--
-- * vers 1.0 - (12/04/2006) Initial
-- * vers 1.1 - (27/11/2006) Changed from "USER_"
-- tables to ALL_.
-- - Added new column "OWNER"
-- - Upper in table_name parameter.
-- * vers 1.2 - (09/05/2008) Not showing table_name
-- when OwnerConstraint<>OwnerTable.
-- Find last version in:
-- http://www.glufke.net/oracle/viewtopic.php?p=2419
-- -------------------------------------------------
SET show off feedback off ver off echo off
SET PAGESIZE 80
COL COLUMNS FORMAT A60
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.R_OWNER) TABELA
, B.COLUMNS
, A.R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS A
, ( SELECT
A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME
, 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
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
FROM ALL_CONSTRAINTS A
, ( SELECT
A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME,
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
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
Postagens
