[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
  

Mensagemem Qua, 12 Abr 2006 5:55 pm

O script abaixo mostra os relacionamentos e as colunas de uma determinada tabela:
Código: Selecionar todos
-- -------------------------------------------------
-- 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:
Código: Selecionar todos
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_MES         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.
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Qui, 13 Abr 2006 11:56 am

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 ?
ualex
Localização: Bauru - SP

Mensagemem Qui, 13 Abr 2006 2:06 pm

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
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Qua, 03 Mai 2006 10:19 am

valeu :)
ualex
Localização: Bauru - SP

Mensagemem Sex, 09 Mai 2008 2:38 pm

Colocado outra correção hoje:

A linha
Código: Selecionar todos
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.OWNER) TABELA


passou a ser assim:
Código: Selecionar todos
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.R_OWNER) TABELA


:-o
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Sex, 05 Jun 2009 10:40 am

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
:(
Wolnei Alves
Localização: Belo Horizonte - MG

Wolnei Alves

Mensagemem Sex, 05 Jun 2009 11:00 am

Essa tabela tem constraints do tipo R ?

Eu acabei de rodar aqui no Oracle 11g e deu certo:
Código: Selecionar todos
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í:

Código: Selecionar todos
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>
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Sex, 05 Jun 2009 11:07 am

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

Um abraço e obrigado.
Wolnei Alves
Localização: Belo Horizonte - MG

Wolnei Alves

Mensagemem Sex, 05 Jun 2009 11:26 am

Código: Selecionar todos
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.
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Sex, 05 Jun 2009 11:46 am

Amigo,

Dei o comando
isso quer dizer que o tipo é P

Código: Selecionar todos
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
Localização: Belo Horizonte - MG

Wolnei Alves

Mensagemem Sex, 05 Jun 2009 11:54 am

Amigo,

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

Código: Selecionar todos
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?
Wolnei Alves
Localização: Belo Horizonte - MG

Wolnei Alves

Mensagemem Sex, 05 Jun 2009 1:58 pm

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.
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Sáb, 06 Jun 2009 2:09 pm

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!
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

Mensagemem Dom, 07 Jun 2009 4:18 pm

Ta aí, é pra ser usado :-)
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem