One of the nice features of a true relational database is that all your tables and columns are described in other tables and columns stored in the same database. These special tables are often called the "system catalog" or in the case of SQL Anywhere the "system tables". While you aren't allowed to change these tables with INSERT, UPDATE or DELETE you can write queries using SELECT.

For example, the following query displays all the fully-qualified column names in your database, sorted in the same order as the columns appeared within the original CREATE TABLE commands:

SELECT sys.sysuserperm.user_name + '.' + sys.systable.table_name + '.' + sys.syscolumn.column_name FROM sys.systable, sys.syscolumn, sys.sysuserperm WHERE sys.systable.table_id = sys.syscolumn.table_id AND sys.systable.creator = sys.sysuserperm.user_id AND sys.sysuserperm.user_name NOT IN ( 'SYS', 'dbo' ) AND sys.systable.table_name NOT LIKE 'pbcat%' ORDER BY sys.sysuserperm.user_name, sys.systable.table_name, sys.syscolumn.column_id

The system tables are described in the SQL Anywhere Help file, but it's easier to use an HTML-based "Data Dictionary" showing all the SQL Anywhere 5.5 System Tables.

This dictionary was created by eDict, which is the code name for a Data Dictionary program that generates HTML documentation for relational databases.

eDict isn't just for documenting the system tables, it can be used for application databases too. And the best part? It generates the dictionary from the database itself so it's never out of date.

eDict is currently in development for the following systems:

• Sybase Adaptive Server Enterprise 11.x
• SQL Anywhere 5.5
• Adaptive Server Anywhere 6.x
• Oracle 8.x

eDict Principles, Objectives and Success Criteria

• Keep it simple.



• Easy to publish, easy to browse.



• Easy to see...
  • ...all the tables in alphabetic order
  • ...all the columns in each table
  • ...all the descriptions in a separate scrolling frame
  • ...all the physical details for each column...
    • Data type (integer, varchar ( 255 ), etc.)
    • NULL versus NOT NULL
    • DEFAULT value
    • Column CHECK condition
    • Participation in primary key, foreign keys and indexes


• Easy to jump...
  • ...to the descriptions for each table and column
  • ...back to the table and column definitions from the descriptions
  • ...to related tables (parents, children, etc.)
  • ...to columns in related tables (foreign key columns, etc.)


• Easy to trust...
  • ...the dictionary is generated from the database itself.


• Easy to update the table and column comments.



• The main audience is application programmers and power users.



• Architects and administrators may or may not find it helpful.



• Formatting of the descriptive text is under user control.



• Links to external documentation may be embedded within descriptions.



• Cooperates with design tools like PowerDesigner and ERwin but not dependent on them.



• Supports some DBMS-specific features, but it's a documentation rather than design tool.

Some eDict Design Decisions

• HTML is used rather than Windows Help or a proprietary format.



• Static HTML files are generated for speed and archiving of old dictionaries.



• The dictionary is generated from the actual database, not a design tool.



• Descriptions are stored for each table and each column:
  • COMMENT ON commands for SQL Anywhere 5.5
  • ZCOMS table provided for Adaptive Server Enterprise 11.5


• 3-frame design:
  • Table of Contents jump list
  • CREATE TABLE commands
  • Descriptions of the tables and columns


• Inter- and intra-frame links displayed differently:
  • Inter-frame links shown in normal font
  • Intra-frame links shown in italics


• HTML formatting and link tags may be embedded within descriptions.



• Options available:
  • Generate separate HTML pages for faster loading with very large designs.
  • Generate single HTML pages for faster navigation with smaller designs.
  • Load descriptions from an external source.