Some DBMS (e.g. Oracle or PostgreSQL) allow to describe the database objects by assigning comments to them. This gives the database administrators, developers and users of reporting and other specialized db tools a possibility to learn the purpose of the tables and columns without needing to read the documentation (or when the documentation is not available).
Different database types allow to describe different types of objects. In general, these are tables, views and their columns. Some DBMS like PostgreSQL also allow to describe other types of objects like schemata, functions, procedures etc.
You can work with database object descriptions either using the Database Tour interface or using SQL code in accordance to your database documentation.
Specifying Database Objects Descriptions by SQL
Unfortunately, each DBMS has its own SQL extension for describing database objects. There is no a unified set of SQL commands, which works for all database types.
Here is an example of describing a table and its columns in PostgreSQL and Oracle databases:
COMMENT ON TABLE detail.payment IS 'Customer Payments';
COMMENT ON COLUMN detail.payment.id IS 'Unique record id';
COMMENT ON COLUMN detail.payment.payment_amount IS 'Payment amount in US dollars';
In MySQL databases, there are no separate commands to describe objects; the describing is done using CREATE TABLE and ALTER TABLE commands:
CREATE TABLE world.cities (
`ID` int(11) NOT NULL COMMENT 'Primary key',
`Name` char(44) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'City name',
`District` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'District name',
`Population` int(11) NOT NULL DEFAULT 0 COMMENT 'City population',
PRIMARY KEY(`ID`)
);
ALTER TABLE world.cities COMMENT 'List of cities';
In SQL Server databases, we can describe the table and its columns using MS_Description extended property. But note, that you have to use different commands in order to add, modify or delete descriptions, and this can be a real headache. Here is how to add extended property:
-- Add table comments
EXECUTE sp_addextendedproperty
N'MS_Description'
,N'List of cities'
,N'SCHEMA'
,N'dbo'
,N'TABLE'
,N'city'
,NULL
,NULL
GO
-- Add column comments
EXECUTE sp_addextendedproperty
N'MS_Description'
,N'Primary key'
,N'SCHEMA'
,N'dbo'
,N'TABLE'
,N'city'
,N'COLUMN'
,N'id'
Working with Database Objects Descriptions in GUI
Database Tour facilitates working with database objects descriptions by providing unified interfaces for this. Besides viewing and editing the descriptions, you can use them also in data migration and export tool.
Please note that currently Database Tour interface can work with database objects descriptions in Oracle, PostgreSQL, SQL Server, and MySQL databases. For other database types, which support object descriptions, you have to write and execute SQL code.
In Database Tour, you can work with object descriptions either individually (e.g. when selecting a table and modifying the table's properties or a table's column) or by using a special tool which allows you to work with all table and column descriptions in the database (see below).
Tool to specify multiple descriptions
To invoke a tool to work with multiple table and column descriptions, please choose Tools | Object Descriptions... menu.
Here, you first load the corresponding attributes of needed tables and their columns (including already existing descriptions (if any)) using a set of object filtering options. Then, you add or modify descriptions for needed objects in a convenient table form.
For columns, it is possible to choose the description from drop-down list of other columns' descriptions with the same name. I.e., first descriptions should be added from scratch; then, the more descriptions you add, the more items will be in the drop-down lists.
Additional find and replace functionality helps to quickly find needed objects or descriptions and make mass replacement if necessary.
You can apply the descriptions to objects either directly from this window or generate the corresponding SQL commands for this. The generated SQL script(s) can then be applied in the current database or in other databases with similar structure.
Finally, you can export all the grid rows to several file formats like Excel, HTML, CSV etc. Exported descriptions can be then loaded to you documentation systems, for example.
See also
