[Hint] Show relationships of a table

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 12 Apr 2006 5:55 pm

The script below shows the relationships and the columns of a given table:
Code: Select all
-- -------------------------------------------------
-- 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
Example:
Code: 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_MES         NM_USUARIO                                                   COSM_USUA_FK
      TB_CONTATO                     NM_USUARIO                                                   CONT_USUA_FK
...
etc
:-
Last edited by dr_gori on Fri, 09 May 2008 2:36 pm, edited 3 times in total.
dr_gori
Location: 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

Poston Thu, 13 Apr 2006 11:56 am

TABLE NAME: CLIENT SP2-0734: unknown command \"AND BUILD ...\"-remainder ignored line.
SP2-0734: unknown command \"AND the CONS ...\"-remainder ignored line.
SP2-0734: unknown command \"ORDER BY t...\"-remainder ignored line.
WHERE the. R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ' CLIEN error on line 73: ORA-00907: missing right parenthesis I use oracle client: SQL * Plus: Release 10.1.0.2.0-Production on Thu Apr 13 2006 11:50:44 but the Bank is 9i, you got any ideas?
ualex
Location: Bauru - SP

Poston Thu, 13 Apr 2006 2:06 pm

The problem is that I had a blank space on that line.
Already corrected here in the source.

Simply remove the space and send bullet! :-)

:-o
dr_gori
Location: 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

Poston Wed, 03 May 2006 10:19 am

Thanks:)
ualex
Location: Bauru - SP

Poston Fri, 09 May 2008 2:38 pm

Placed another correction today: the line
Code: Select all
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.OWNER) TABELA
became like this:
Code: Select all
, ( SELECT TABLE_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME AND OWNER=A.R_OWNER) TABELA
:-
dr_gori
Location: 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

Poston Fri, 05 Jun 2009 10:40 am

Comrade, I ran your SCRIPT but it does not show anything.

SQL > @cons SQL > record NAME: TABLE not bringing any reply I'm working with Oracle 10 g.
a hug:(
Wolnei Alves
Location: Belo Horizonte - MG

Wolnei Alves

Poston Fri, 05 Jun 2009 11:00 am

This table has constraints of type R?

I just run here in Oracle 11 g and it worked:
Code: 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>
Tai:
Code: 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>
dr_gori
Location: 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

Poston Fri, 05 Jun 2009 11:07 am

Sorry buddy, but as I know and the tables have constrains the type r.

A hug and thank you.
Wolnei Alves
Location: Belo Horizonte - MG

Wolnei Alves

Poston Fri, 05 Jun 2009 11:26 am

Code: Select all
select CONSTRAINT_NAME, constraint_type, TABLE_NAME from all_constraints where table_name = ' YOURTABLENAME '
that 's. The CONS script.SQL checks whether the table has not only parents but also children. Extremely useful to \"meet\" a table and where it is used.
dr_gori
Location: 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

Poston Fri, 05 Jun 2009 11:46 am

Friend, I gave the command that means the type is SQL > select CONSTRAINT_NAME P, TABLE_NAME constraint_type, 2 from all_constraints where table_name = ' REGISTER ';

TABLE_NAME, CONSTRAINT_NAME C-------------------------------------------------------------CHCADASTRO REGISTER SYS_C006366 REGISTER SYS_C006367 REGISTER C P C 3 rows selected.

SQL > where have R in the script I change for P is IT?

Hug and thank you.
Wolnei Alves
Location: Belo Horizonte - MG

Wolnei Alves

Poston Fri, 05 Jun 2009 11:54 am

Friend, I put in the kind of constraints \"P\" and ran the script he brought the following reply SQL > @cons TABLE NAME: RECORD OWNER TYPE TABLE-----------------------------------------------------------------COLUMNS------------------------------------------------------------R_CONSTRAINT_NAME------------------------------CDTIPOCADASTRO NRCADASTRO ARCETIL FATHER, this table has a relationship with other what is not correct? am I doing something wrong?
Wolnei Alves
Location: Belo Horizonte - MG

Wolnei Alves

Poston Fri, 05 Jun 2009 1:58 pm

P are PRIMARY KEY Constraints.

I think you need to better understand the concept behind the constraints. What the script shows are two things: * If the table has \"sons\" and the details (field that lists) * if the table has \"PARENTS\" and the details.

What is this?

If she has CHILDREN, means that there are x, y, and z tables that have a FK if linking with the PK of the table that you reported.

If she has PARENTS, then it means that this table you entered \"it depends\". I.e. There is a FK that points to the PK of another table.
dr_gori
Location: 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

Poston Sat, 06 Jun 2009 2:09 pm

Congratulations Thomas ... muito bom! muito show! :P Broke a galhão ... I always wanted to do a screen to show the newbies there in the relationship of the tables.

I'm going to make a view that there going to pack a wallop!

Can I? aheauheauehauheauhea always had laziness (time) start ...

Big Hug!
RodrigoValentim
Location: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

Poston Sun, 07 Jun 2009 4:18 pm

Ta there, is to be used:-)
dr_gori
Location: 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


  • See also
    Replies
    Views
    Last Post


                Return to SQL

                Who is online

                Users browsing this forum: No registered users and 3 guests