Documenting Your Tables and Columns in Oracle

If you ask most developers if they would prefer to get a root canal or write software documentation, most would choose the root canal.  Almost everyone universally hates to write documentation.  But in many cases, at least some amount of documentation for a system is essential.

This is very much true for the tables and columns in your database.  At some point, someone is going to need to know what data is stored in your database, what assumptions you made and what some non-intuitive column name really means.  Ideally, yes, we would like every table and column to have a meaningful name that was descriptive enough such that our database was self documenting.  But we all know this is rarely the case.

In Oracle, you can use the COMMENT ON command to put comments on tables and columns.  The exact syntax is shown below:

-- Setting comments on a table
COMMENT ON TABLE <<table name>> IS ‘<<comment text>>’;


-- Setting a comment on a columns
COMMENT ON COLUMN <<table name>>.<<column name>>
    IS ‘<<comment text>>’;

To add comments in this way, you need to either be the schema owner on the object or have the COMMENT ANY TABLE privilege.

It is pretty simple, the comment text that you enter will be stored as a comment with the table or column respectively.

Where is this comment stored?  It is stored in Oracle's data dictionary and you can access it through two different views, all_tab_comments and all_col_comments.  (You can also use the user or dba version of these views as well).

SELECT * FROM all_tab_comments;

SELECT * FROM all_col_comments
    WHERE table_name = ‘<<table name>>’;

By themselves, these views only provide the comments.  But you can easily join these views to other views in the data dictionary to get a more complete view of what is going on in your database.  Take for example the following query which joins the user_tab_columns and user_col_comments views in order to list all of the columns for a table with their data type, if the column is nullable and any comments entered for the column.

SELECT tc.column_name, tc.data_type, tc.data_length,
    tc.data_precision, tc.data_scale, tc.nullable,
    cc.comments
FROM user_tab_columns tc
LEFT OUTER JOIN user_col_comments cc
    ON tc.table_name = cc.table_name
    AND tc.column_name = cc.column_name
WHERE tc.table_name = ‘<<table name>>’
ORDER BY tc.column_id;

Now, you have not just the column name and whatever data in the column to go on, you potentially have comments left by who designed this table or added this column about what their intent for this column was.  If you have ever taken over supporting an application and its database that has been around for a while, you know how useful this can be.

Success Factors

So that is the mechanics of how to add a comment for a table or column.  But how do we use this to solve the question posed in the beginning, the fact that everyone hates doing documentation.  Here are some tips:

• DO focus on commenting tables or columns whose meaning is not immediately obvious.  Focus on columns that have cryptic names and the parts of your database that are difficult to understand.  Maximize your time by spending it on places where concepts or intent is not obvious and people may trouble understanding a year down the road.
• DON'T comment every column just to say you have every column commented.  We all know what the purpose of the FIRST_NAME column is.  Spend your time where there is value.
• DO store comments for tables and columns in the database like this.  The problem with putting data definitions in a project document is they get filed away with the project.  And then someone has to remember what project added a table or column and go look up a document in a separate system.  Use Oracle's built in commenting feature so the definitions you create will be at your fingertips when ever you need them.
• DON'T ignore existing tables in your database for commenting.  We've all been assigned to take over an application and had occasion when we had to reverse engineer what a table or column really meant.  That is the hard work.  If you go through all of this effort, take the simple step of storing what you discovered as a comment on the table or column.  This way it will be written down somewhere for three months from now when you need to remember the definition again.
• DO realize that documenting your system is an important part of your job.  Hey, not every part of our job is fun.  That is why it is called work.  But you do have a responsibility to other professionals in your organization to record some basic notes about how you designed and built your database.  You don't have to write Shakespeare, but some basic, functional docmentation is not too much to ask.

The full documentation for the COMMENT statement in Oracle 12c is here.