SQL tool to represent the table hierarchical structure of an user / schema of an Oracle (relational)database

From: Valentin Welter <valentin.welter_at_t-online.de>
Date: 4 Feb 2003 10:37:59 -0800
Message-ID: <7f0ff6a5.0302041037.6ebc81b8_at_posting.google.com>



[Quoted] You find this item on my homepage
http://valentin.welter.bei.t-online.de

Database Hierarchy SQL Tools

  1. DBHierarchy.sql This tool creates an output file, which shows the table structural hierarchy of an user/schema of an Oracle (relational) database. You can use the tool both under Windows and Unix.

Launching
There are two possibilities to launch the tool under Windows:
· Start SQL*Plus tool and enter the command: _at_<path>DBHierarchy
· On the MS DOS Window or under Unix enter the command:
sqlplus <user>/<password>_at_<database> @<path>DBHierarchy

After launching the script asks for:
· Database (because of compatibility with Oracle versions,
which do not have the view v$database)

· User / schema – it could be different of <user>/<password> on
the command line.

· The output files directory.

The script creates two files:
· DBHierarchy<database><user/schema>.LST
This file contains the table structural hierarchy of the user / schema.
· CrossRef<database><user/schema>.LST
This file contains the parent – children table relationships of the user / schema. It is used at the DBTree tool. See http://valentin.welter.bei.t-online.de - Demos & More.

2. DBChildrenAndParents.sql
This tool is similar to the DBHierarchy.sql tool. It creates an output file, which shows all children and grandchildren of a table. The file shows also all parents, but no grandparents, of a table

Launching
There are two possibilities to launch the tool under Windows:
· Start SQL*Plus tool and enter the command:
_at_<path>DBChildrenAndParents
· On the MS DOS Window or under Unix enter the command:

sqlplus <user>/<password>_at_<database> @<path>DBChildrenAndParents

After launching the script asks for:
· Database (because of compatibility with Oracle versions,
which do not have the view v$database)
· User / schema – it could be different of <user>/<password> on
the command line.
· The table, for which the children and parents have to been
listed.
· The output files directory. By default Oracle creates the
output files in the <Oracle Home Path>\bin directory. The script creates the file Children<database><user/schema><table>.LST

Remarks
· The scripts create a at the run time a couple of temporary
tables.
· Oracle produces an endless loop when a table is a child or
grandchild of itself.

The entries where the child table is the same like the parent table are removed from the temporary tables.
The grandchildren, which are identical to their parent table can not be removed.

Have a lot of fun! Received on Tue Feb 04 2003 - 19:37:59 CET

Original text of this message