[Tip] Show relationships in a table

Wed, 12 Apr 2006 5:55 pm

The script below shows the relationships and columns of a given table:

-- -------------------------------------------------  
-- 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://en.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

Example:

Select all

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

ualex · Thu, 13 Apr 2006 11:56 am

Select all

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

I use the Oracle Client:
SQL * Plus: Release 10.1.0.2.0 - Production on chi Apr 13 11:50:50 PM

But the bank is 9i, do you have any idea?

Thu, 13 Apr 2006 2:06 pm

The problem is that it had a blank space in this line.
I have corrected here at the source.

Just remove space and send bullet!

: -O

ualex · Wed, 03 May 2006 10:19 am

it cost

Fri, 09 May 2008 2:38 pm

Placed another correction Today:

The line

Select all

, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.OWNER) TABELA

was thus:

Select all

, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.R_OWNER) TABELA

]] :-O

Wolnei Alves · Fri, 05 Jun 2009 10:40 am

Comrade,
I performed your script but it shows nothing.
SQL> @cons
Table Name: Registration
SQL>

Not bringing any response

I'm working with Oracle 10g.
A hug

Fri, 05 Jun 2009 11:00 am

Does this table have constraints of type r?

I just run here in Oracle 11g and worked:

Select all

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

Select all

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 · Fri, 05 Jun 2009 11:07 am

Sorry friend, but as I know and the tables have constrains of type R.

a hug and thank you.

Fri, 05 Jun 2009 11:26 am

Select all

select CONSTRAINT_NAME, TABLE_NAME, constraint_type from all_constraints where table_name='SUATABELA'

there that is. The Script Cons.SQL checks not only if the table has parents but also children ... extremely useful to "know" a table and where it is used.

Wolnei Alves · Fri, 05 Jun 2009 11:46 am

Friend,

I gave the command
this means that the type is p

Select all

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>

where he has R in the script I change for p

Is this?

Embrace and thank you.

Wolnei Alves · Fri, 05 Jun 2009 11:54 am

Friend,

I put in the type of constraints "p"
and I executed the script
he brought the following answer

Select all

SQL> @cons 
TABLE NAME : CADASTRO 
 
TIPO  OWNER                          TABELA 
----- ------------------------------ ------------------------------ 
COLUMNS 
------------------------------------------------------------ 
R_CONSTRAINT_NAME 
------------------------------ 
PAI   ARCETIL 
CDTIPOCADASTRO, NRCADASTRO

This table has a relationship with others
what is not correct? Am I doing something wrong?

Fri, 05 Jun 2009 1:58 pm

Contraints p are primary key.

I think you need to better understand the concept behind the constraints. What the script shows are two things:
* If the table has "children" and the details (field that relates)
* If the table has "Parents" and the details.

What is this?

If she has children, it means that there are tables x, y and z that has a fk relating to the PK of the table that you reported.

If she has parents, then it means that this table you informed "depends on" others. That is, there is an FK that points to PK from another table.

Sat, 06 Jun 2009 2:09 pm

Congratulations thomas .. muito good !!! muito show! : P

broke a gallon ... I always wanted to make a screen to show the beginners there in the sector the relationship of the tables ...

I'm going to make a I'll see it and send it!

Can I? Aheauheauehauheahea

I have always been lazy (time) to begin ...

Abração!

Sun, 07 Jun 2009 4:18 pm

Ta, it's to be used

[Tip] Show relationships in a table

Who is online