Managing Table and Column Comments in Oracle SQL Developer

thatjeffsmith SQL Developer 12 Comments

Tell Others About This Story:

An interesting question came up on the ORACLE-L mailing list this morning regarding table comments:

Just wanted to get a general opinion on using comments in the database. For example COMMENT ON COLUMN table.column “No comment”.

Does anyone do this as a regular best practice? I would prefer to see this abstraction in a data model, maybe not pushed to the database layer, especially since not all RDBMS engines have “comment” functionality.

The thought is we should do this for every column, every table, every oracle db. This ends up being several million comments added to the our various instances.

For once I decided to respond. I usually stay away from answering posts here as I’ve been out of the ‘real world’ for way too long, but this one stuck a little close to home.

My reply:

If you keep them in the data model, you assume that everyone has a copy of the data model.

If you put them in the database, then folks using your database will have access to your comments.

I prefer both – write at design time in your data model, and also include in your database objects. I’ve had to help too many end users with poorly documented systems to rely on having a good data model around.

Jeff

So, if you happen to agree with me, how can you achieve this?

Reading Existing Comments in Tables

It’s pretty straightforward to read the column comments – we show those in the Columns page of a table (or view) editor.

Table Column Comments

Table Column Comments

The table itself can be commented. You’ll find these in the Details page:

Table Comments

Table Comments

All I See Are a Bunch of NULLS!

No comments? This is a pretty common occurrence. It doesn’t necessarily mean the system is undocumented, it just means maybe the comments are somewhere else. Do you have a data model? Maybe they are there.

If not, you could create a new data model based on your existing SCHEMA(s). Once you had the model in hand, you could document your objects to your desire. You would then need to decide whether or not to propagate those changes back to your database – or just leave them in the model.

Or, you could just directly add the comments to your database objects. SQL Developer’s Table Editor allows you to do both. You can also right click on a table and say Column > Comment or Table > Comment. Let’s take a look at how to do this in the modeler though.

Commenting Objects In Your Oracle SQL Developer Data Modeler Model

I’m going to start by importing a table sans comments from the data dictionary to a new model.

  CREATE TABLE BEER2 
   (	BREWERY VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	CITY VARCHAR2(100 BYTE), 
	STATE VARCHAR2(100 BYTE), 
	COUNTRY VARCHAR2(100 BYTE), 
	ID NUMBER
   ) ;

Forget how to reverse engineer a table to a model? You’ve got 2 ways:

  1. Drag one or more tables directly to a model
  2. Import the Data Dictionary

For this exercise I’m going the quick and dirty way, drag and drop!

Here, watch ‘the movie.’

Just drag your table to a model

Just drag your table to a model

So now that the table is in our model, let’s add some comments.

Model Comments vs RDBMS Comments
When opening the object editor, you’ll see panels and pages for both ‘Comments’ and ‘Comments in RDBMS.’ RDBMS stands for Relational Database Management System. Where you see ‘Comments,’ anything written there will ONLY be stored in the model. Where you see ‘RDBMS,’ anything written there will be included with the object DDL scripts.

Column Comments

These column comments are ONLY in the model

These column comments are ONLY in the model

These comments get stored with the table in the database

These comments get stored with the table in the database

Table Comments
Same difference here, and I’m not going to bore you or your internet connection with 2 more similar pictures.

Moving Comments From the Model to the Database

You want to ‘Synchronize the Data Dictionary with Model.’ That’s the button that points to the right in the modeler toolbar. That will take a look at the database objects you imported, compare them to your updated model, and show you what’s changed since.

The Compare shows that my model has comments that the database doesn't...

The Compare shows that my model has comments that the database doesn’t…

And if I click the ‘DDL Preview’ button I can get the script I need to run against the database to bring it into ‘synch’ with my model.

Alter Scripts to post your comments back into your database objects!

Alter Scripts to post your comments back into your database objects!

So Back to the Oracle-L Thread…

My favorite bullet-proof DBA, @DBAKevlar had a great comment on the subject. If you’re going to use comments, make them meaningful. Everything comes with a cost. Don’t say ‘Column X is for storing X.’ Try to put some business logic behind those comments.

Tell Others About This Story:

Comments 12

  1. I have a table that ends up being extremely long when I add a Comment in RDBMS. Is there a way to shift the comment to take up one row that is broken in two pieces? Or can it only be represented in one long row?

    This is how it reads now:
    Example of my Comment in RDBMS that is very long.

    How I want it to read:
    Example of my Comment in
    RDBMS that is very long.

    1. thatjeffsmith Post
      Author

      if you use the reporting repository, you can then build queries for your reports, and do whatever you want with the text, for example use regex or substr functions to insert html BR tags every 60 chars in the comment fields

  2. Thank you! Took a few pages through Google to find someone who can design a site to I can read it… Everyone else has pages and pages of text and no simple “here is the answer”.

    1. thatjeffsmith Post
      Author
  3. Jeff: I have a question about how to include comments to a table in SQL developer. The comments basically explains what the table does. There are some of the tables I am working on that already have comments, however a lot of them don’t have any.
    Example: Table — FIXBADID with OWNER: TT_EAS. I was wondering what the query will be to include comment to this table. Thanks

    1. thatjeffsmith Post
      Author
      1. I want to ALTER syntax to ADD them when they’re missing. I saw this query on Oracle but I wasn’t sure if it also works with SQL developer. The query is below
        COMMENT ON TABLE “TABLE NAME”
        IS
        ‘COMMENTS.’;

        Thanks

        1. thatjeffsmith Post
          Author

          column comment example

          [sql]
          comment on column "HR"."BEER"."CITY" is ‘Corporate is at HQ’
          [/sql]

          You can of course use SQL Developer to add the comments to the tables and their columns. Just find the table in the browser and right-click on it 🙂

          1. I used the COMMENT ON TABLE “TABLE NAME”
            IS
            ‘COMMENTS.’;. It worked. Thanks so much for your help.

  4. Another point about comments in the database – most (if not all) modern reporting and BI tools read comments in the Oracle data dictionary into the various meta layers. This is hugely helpful to folks doing “self-service” BI and need to build useful reports with meaningful business meta data that end users relate to.

    And who does not run reports on their database? Your comments WILL get used. So, just do it!

Leave a Reply

Your email address will not be published. Required fields are marked *