[Dica] Mostrar relacionamentos de uma tabela
Enviado: Qua, 12 Abr 2006 5:55 pm
O script abaixo mostra os relacionamentos e as colunas de uma determinada tabela:
Exemplo:
-- -------------------------------------------------
-- 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
SQL> @CONS
TABLE NAME : TB_USUARIO
TIPO TABELA COLUMNS R_CONSTRAINT_NAME
----- ------------------------------ ------------------------------------------------------------ ------------------------------
PAI TB_GRUPO_FUNCAO CD_GRUPO_FUNCAO CP_GRUPO_FUNCAO
TB_PESSOA_EMPRESA CD_PESSOA_EMPRESA CP_PESSOA_EMPRESA
TB_SETOR CD_PESSOA_UNIDADE, CD_DEPARTAMENTO, CD_SETOR CP_SETOR
FILHO TB_ACESSO_USUARIO NM_USUARIO CE_ACESSO_USUARIO2
TB_AGENDA_VISITA NM_USUARIO AGVI_USUA_FK
TB_ANALISE_ACEIT_RISCO NM_USUARIO ANAR_USUA_FK
TB_ARQUIVO_EMBARQUE NM_USUARIO_CRIOU CE_ARQUIVO_EMBARQUE1
TB_ARQUIVO_EMBARQUE NM_USUARIO_LEU CE_ARQUIVO_EMBARQUE2
TB_BOLETO NM_USUARIO_CANCELAMENTO TBBLT_TBUSER_CANC_FK
TB_BOLETO NM_USUARIO_CRIACAO TBBLT_TBUSER_FK
TB_CAIXA_POSTAL NM_USUARIO CAPO_USUA_FK
TB_CARTA_PARCELAS NM_USUARIO_EMITENTE CE_CARTA_PARCELAS3
TB_CLASSIF_PESSOA NM_USUARIO CLPE_USUARIO_FK
TB_CLIENTE NM_SOLICITANTE_CADASTRO TBC_TBUSER_SOL_FK
TB_CLIENTE NM_USUARIO_ATUALIZACAO TBC_TBUSER_AT_FK
TB_CLIENTE NM_USUARIO_CADASTRO TBC_TBUSER_CAD_FK
TB_COLETOR NM_USUARIO COLE_USUA_FK
TB_COMISSAO_SEGURO_mês NM_USUARIO COSM_USUA_FK
TB_CONTATO NM_USUARIO CONT_USUA_FK
...
etc