Set Table/Column Descriptions

Most databases allow a textual descriptive label attached to a table, view, table column (never a view column!) or other database object type. It is called either description, or comment, or remark. It can be set through the SQL script or using a GUI tool. When set through scripting, most databases follow Oracle’s non-standard SQL style COMMENT ON TABLE/VIEW/COLUMN name IS ‘…’. With small variations and restrictions from one database type to another, this is followed by Oracle, PostgreSQL, Amazon Redshift, IBM Db2, SAP/Sybase SQL Anywhere, and Firebird.

MySQL, MariaDB and Amazon Aurora use a rather different convention: you can append COMMENT = ‘…’ after a table or column declaration, or after a separate ALTER TABLE statement.

In Microsoft Access, object descriptions can be set through the GUI interface, in either the Properties popup, or inline for the column definitions, in the last grid’s column.

SQL Server has no specialized comments, but you can add object descriptions disguised as extended properties, as property-value pairs.

Import Descriptions and Remarks

As per version 2 or all our Xtractor applications, we import table/view/column comments or remarks from MySQL and MariaDB, Oracle and DB2, PostgreSQL and Redshift, SQL Anywhere, Firebird and MS Access. We ignore any non-standard and hard to parse comments from SQL Server and Azure, SQL Server CE and SQLite.

• MySQL and MariaDB – we extract TABLE_COMMENT from INFORMATION_SCHEMA.TABLES, and COLUMN_COMMENT from INFORMATION_SCHEMA.COLUMNS. View comments are not allowed.
• PostgreSQL and Amazon Redshift – table/view/column descriptions are all stored in the description field of the pg_description schema table.
• Oracle – all_tab_comments schema table has a comments field with all table/view/column descriptions.
• IBM Db2 – the remarks field from either sysibm.systables or sysibm.syscolumns returns table or column descriptions. View descriptions or remarks are not supported.
• SAP/Sybase SQL Anywhere – table/view/column descriptions are all returned by the remarks field of the sysremarks schema table.
• Firebird – comments are returned by the rdb$description field of either the rdb$relations or rdb$relation_fields schema table.
• Microsoft Access – we find table/view/column comments in the description fields of either the OleDbSchemaGuid.Tables or OleDbSchemaGuid.Columns schema tables.

Display and Customize Descriptions

Imported descriptions are displayed as customizable values in the object properties grid. Go to the first Objects tab with either a table, a view, or a table column selected. You’ll see its Description field value in bold, that could be changed with your own custom text. This will not save the description in the database, but will only provide an alternative display value in current Xtractor project.

Descriptions will also appear now as tooltips in model diagrams, when you move the mouse over a shape title, for a shape representing a table or view. Or over a shape item connected to a table column.

Tooltips are enabled or disabled for the whole application – for diagrams, lists or trees – by the View – Tooltips main menu command. With tooltips enabled, we also show information for connections between table shapes, when you move the mouse over a link.

Original Table and Field Names

Databases accessed directly often expose cryptic unfriendly table or column names. We also generate relationship items with simple names, pointing to the table name at the other end of the relationship, eventually made unique with a _1/2/3… suffix. Both models and queries get by default an internal unique cryptic name (that you cannot change) and a visible customizable alias.

Here is what a query on the original emp–dept sample database may look like:

Custom Aliases

The good news is we worked hard to allow you to customize any of these elements with your own aliases. An alias is always saved in your system database, never in the remote connected database. In fact, you may never ever change anything on the remote connected database, that’s by design.

Let’s have a complete makeover of our two tables in a model diagram. Collapse all relationships, then change shape titles with Edit Alias contextual command. Change then all shape items, including relationship items, with the similar Edit Alias command. On the left there is what you started with, on the right there are the two tables with new more friendly aliases:

Alias Change Propagation

The beauty is such changes in the model automatically propagate to all stored queries and models in your system databases, including your open documents. Just switch back to your previous query and you’ll have a pleasant surprise: new aliases propagated everywhere and there is nothing left to do for you, to get the new look:

New aliases replaced everywhere the database names. However, original names will still be automatically used in generated SQL (because your database knows nothing about your aliases). Aliases are prone to less restrictions then table and column names. They may contain spaces, be case sensitive, have no length limit. Whenever you want to go back to the original database name, just leave the edit field empty.