[Dica] Mostrar relacionamentos de uma tabela

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
dr_gori
Moderador
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

O script abaixo mostra os relacionamentos e as colunas de uma determinada tabela:

Selecionar tudo

-- ------------------------------------------------- 
-- 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
Exemplo:

Selecionar tudo

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
:-o
Editado pela última vez por dr_gori em Sex, 09 Mai 2008 2:36 pm, em um total de 3 vezes.
ualex
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Ter, 01 Nov 2005 1:25 pm
Localização: Bauru - SP

Selecionar tudo

TABLE NAME : CLIENT
SP2-0734: início de comando desconhecido "AND CONSTR..." - restante da linha ignorado.
SP2-0734: início de comando desconhecido "AND A.CONS..." - restante da linha ignorado.
SP2-0734: início de comando desconhecido "ORDER BY T..." - restante da linha ignorado.
WHERE A.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'CLIEN
                                                                                                    
ERRO na linha 73:
ORA-00907: parentese direito ausente
eu uso o client do oracle :
SQL*Plus: Release 10.1.0.2.0 - Production on Qui Abr 13 11:50:44 2006

mas o banco é 9i , você tem alguma ideia ?
Avatar do usuário
dr_gori
Moderador
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

O problema é que tinha um espaço em branco nessa linha.
Já corrigi aqui no fonte.

Basta retirar o espaço e mandar bala! :-)

:-o
ualex
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Ter, 01 Nov 2005 1:25 pm
Localização: Bauru - SP

valeu :)
Avatar do usuário
dr_gori
Moderador
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

Colocado outra correção hoje:

A linha

Selecionar tudo

, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.OWNER) TABELA 
passou a ser assim:

Selecionar tudo

, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.R_OWNER) TABELA 
:-o
Wolnei Alves
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Sex, 05 Jun 2009 10:34 am
Localização: Belo Horizonte - MG
Wolnei Alves

Camarada,
Executei o seu SCRIPT mas ele não mostra nada.

SQL> @cons
TABLE NAME : cadastro
SQL>

não trazendo qualquer resposta

Estou trabalhando com o Oracle 10G.
um abraço
:(
Avatar do usuário
dr_gori
Moderador
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

Essa tabela tem constraints do tipo R ?

Eu acabei de rodar aqui no Oracle 11g e deu certo:

Selecionar tudo

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.

SQL> 
Taí:

Selecionar tudo

SQL> @CONS
TABLE NAME : REPCAT$_FLAVORS

TIPO  OWNER                          TABELA                         COLUMNS                                                      R_CONSTRAINT_NAME
----- ------------------------------ ------------------------------ ------------------------------------------------------------ -----------------------------
PAI   SYSTEM                         REPCAT$_REPCAT                 GNAME, GOWNER                                                REPCAT$_REPCAT_PRIMARY
SQL> 
SQL> 
SQL> 
SQL> @CONS
TABLE NAME : REPCAT$_REPCAT

TIPO  OWNER                          TABELA                         COLUMNS                                                      R_CONSTRAINT_NAME
----- ------------------------------ ------------------------------ ------------------------------------------------------------ -----------------------------
FILHO SYSTEM                         REPCAT$_FLAVORS                GNAME, GOWNER                                                REPCAT$_FLAVORS_FK1
      SYSTEM                         REPCAT$_FLAVOR_OBJECTS         GNAME, GOWNER                                                REPCAT$_FLAVOR_OBJECTS_FK1
      SYSTEM                         REPCAT$_REPGROUP_PRIVS         GNAME, GOWNER                                                REPCAT$_REPGROUP_PRIVS_FK
      SYSTEM                         REPCAT$_REPOBJECT              GNAME, GOWNER                                                REPCAT$_REPOBJECT_PRNT
      SYSTEM                         REPCAT$_REPSCHEMA              SNAME, GOWNER                                                REPCAT$_REPSCHEMA_PRNT
      SYSTEM                         REPCAT$_SITES_NEW              GNAME, GOWNER                                                REPCAT$_SITES_NEW_FK2
SQL> 
SQL> 
Wolnei Alves
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Sex, 05 Jun 2009 10:34 am
Localização: Belo Horizonte - MG
Wolnei Alves

Desculpe amigo, mas como eu fico sabendo e as tabelas tem constrains do tipo R.

Um abraço e obrigado.
Avatar do usuário
dr_gori
Moderador
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

Selecionar tudo

select CONSTRAINT_NAME, TABLE_NAME, constraint_type from all_constraints where table_name='SUATABELA'
Aí que está. O script CONS.SQL verifica não só se a tabela tem pais mas também filhos... Extremamente util pra se "conhecer" uma tabela e onde ela é usada.
Wolnei Alves
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Sex, 05 Jun 2009 10:34 am
Localização: Belo Horizonte - MG
Wolnei Alves

Amigo,

Dei o comando
isso quer dizer que o tipo é P

Selecionar tudo

SQL> select CONSTRAINT_NAME, TABLE_NAME, constraint_type 
  2  from all_constraints where table_name='CADASTRO';

CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
CHCADASTRO                     CADASTRO                       P
SYS_C006366                    CADASTRO                       C
SYS_C006367                    CADASTRO                       C

3 rows selected.

SQL> 
onde tiver R no script eu mudo para P

É ISSO?

Abraço e obrigado.
Wolnei Alves
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Sex, 05 Jun 2009 10:34 am
Localização: Belo Horizonte - MG
Wolnei Alves

Amigo,

Coloquei no tipo de constraints "P"
e executei o scritp
ele trouxe a seguinte resposta

Selecionar tudo

SQL> @cons
TABLE NAME : CADASTRO

TIPO  OWNER                          TABELA
----- ------------------------------ ------------------------------
COLUMNS
------------------------------------------------------------
R_CONSTRAINT_NAME
------------------------------
PAI   ARCETIL
CDTIPOCADASTRO, NRCADASTRO
esta tabela tem relacionamento com outras
o que não esta correto? estou fazendo algo errado?
Avatar do usuário
dr_gori
Moderador
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

Constraints P são PRIMARY KEY.

Acho que você precisa entender melhor o conceito por tras das constraints. O que o script mostra são duas coisas:
* Se a tabela possui "FILHOS" e os detalhes (campo que relaciona)
* Se a tabela possui "PAIS" e os detalhes.

O que é isso?

Se ela tem FILHOS, significa que existem tabelas x, y e z que tem uma FK se relacionando com a PK da tabela que você informou.

Se ela tem PAIS, então significa que essa tabela que você informou "depende" de outras. Ou seja, existe uma FK que aponta pra PK de uma outra tabela.
RodrigoValentim
Moderador
Moderador
Mensagens: 367
Registrado em: Ter, 25 Mar 2008 3:41 pm
Localização: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Parabens Thomas.. muito bom!!! muito show! :P

Quebrou um galhão... Sempre quis fazer uma tela para mostrar aos iniciantes lá no setor o relacionamento das tabelas...

Vou fazer uma view disso aí e mandar ver!!!

Posso? aheauheauehauheauhea

Sempre tive preguiça (tempo) de comecar...

Abração!
Avatar do usuário
dr_gori
Moderador
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

Ta aí, é pra ser usado :-)
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Bing [Bot], Google [Bot] e 10 visitantes