More information and other database objects
There is a lot of more information that can be read from the system tables. There are also more things that belong to a database than tables and constraints. The decision of what to display within the diagram is difficult. It may not be a good idea to show all possible information and database objects within one diagram. At the other side, there might be information that is missing for a specific view.
At this point, you should decide what to include and what to eliminate from the scripts provided. Less can be more, so a restriction to show only a subset of features and tables might be helpful.
The additional information of the data types used in the table can be very useful. This information can be selected from the table syscolumns, but the values have to be decoded to a human readable form. The additional information of the data types needs more space, so this should only be an optional feature.
Figure 6. Data types of the columns
The information if there are triggers for insert, update, delete, or select can be displayed as one additional row in the listing of a table, so this information can be added without influence of the appearance.
Alias names for tables, the synonyms may be helpful to find the tables, used in statements. This information can be selected using the tables syssyntable and systables. These synonyms don't have their own columns, and are only alternate names of tables.
Figure 7. Synonyms referencing a table
Views depend on one or more tables, so we can show this relation between a view and the tables, where it is built from. The list of columns makes no sense because a view can contain a lot of functionality, such as SUM, AVG, SUBSTR(), so there is no option to show the relationship between the columns of a view and the columns of the tables.
Figure 8. Views referencing one or more tables
Performance analysis often needs to know what columns are at the first position of an index. In the sample script, these columns are marked with an HTML tag for a different color.
Sometimes objects of a database have different owners. To manage permissions, it is important to know who is the owner of the objects. So we added the value of systables.owner to the table name to show who has created the object.
After listing the features that can be displayed, we provide a way to
reduce the number of tables displayed. In most cases, there is a set
of tables, included in a statement, to be analyzed; you
do not need the several hundreds of tables contained in the database.
Most times, it is useful to select only a subset of tables that
are of interest. We added this option in our script to reduce
the diagram to a defined list of table names. In this sample script, we
included the option to select only one table, using the
-t tabname option, or to select a list of tables using
A relational database provides everything needed to get the information about the structures of the objects and references within the database.
Using this information and adding some information for the display of the objects, every database can be displayed in a graphical mode.