Most relational databases allow you to define comments for their tables, columns, views, and so on. These additional pieces of information are called database meta data or data dictionary. Meta data may be useful for developers or other database users. Meta data allow the development team to understand what various tables or columns are for without having to ask database designer what their intentions were.
Generating meta data in SQL scripts is one of the most often requested Vertabelo features and it’s finally here. Here’s how you can take advantage of the feature.
How to generate meta data for your database in Vertabelo
First, enable comment generation. In the Model Properties panel, find the SQL generation settings sections and check the “Generate comments for database objects” checkbox.
Define comments for you database objects.
For a table or view, fill the Description field in the Table Properties or View Properties panel.
For a column comment, first expand the details section (click on the tiny arrow to the right of the column name) and fill in the Description field.
Generate the SQL script. The comments for your tables, views, and columns will be created in the script.
For MySQL, PostgreSQL, Oracle, HSQLDB, and IBM BD2 databases the descriptions are generated using the COMMENT statement. For MS SQL Server they are generated as the MS_Description extended property. Vertabelo does not generate comments for Sqlite database because Sqlite does not support comment statements.
Import comments into Vertabelo
There are two ways to import your database model into Vertabelo:
- importing an SQL create script,
- using the reverse engineering tool.
Both ways now support database meta data. Any comments defined in the database will now be imported into Vertabelo and put in the description field of the appropriate object.
This is useful if you use Vertabelo to analyze the structure of an unknown database. Vertabelo now extracts not only the database structure but also the meta data defined for the database.
For MySQL, PostgreSQL, Oracle, HSQLDB, and IBM BD2 databases the descriptions are read from the corresponding COMMENT statement. For MS SQL Server they are read from the MS_Description extended property. Vertabelo does not read comments from Sqlite database because Sqlite does not support comment statements.
See comments in your SQL tool
Most database management tools allow you to see the comments defined for tables, views, columns, and other database objects. For example, in Squirrel first select your table in the navigation menu on the left. Go to Info tab and look at the remarks property values.
For some databases you have to enable remarks reporting (how to enable it for Oracle), for others it will work out of the box.
Your developers/database users can see the comments directly in the database.
How would you like us to improve Data Dictionary support in Vertabelo? Let us know in the comments!