G-1270

🆓

Info

Try to define a comment for each column.

Rule Details

Severity Level: Info
Quality Area: Maintainability
Fixing times: 60 mins
Other
Database Support: OracleDB (v12.2.0 +)

Reason

Comments on columns act as built-in documentation that surfaces important information right where it’s needed. When someone uses describe table or SQLcl’s info command, they can immediately see notes explaining the purpose, constraints, or special considerations of each column. This clarity accelerates development, debugging, and onboarding of new team members.

Example

Non-Compliant Example

create table parent (
   parent_id   raw(16) default sys_guid() not null,
   parent_name varchar2(100 char)         not null,
   constraint parent_pk primary key (parent_id),
   constraint parent_uk1 unique (parent_name)
);

Compliant Solution - ★★★★★

create table parent (
   parent_id   raw(16) default sys_guid() not null,
   parent_name varchar2(100 char)         not null,
   constraint parent_pk primary key (parent_id),
   constraint parent_uk1 unique (parent_name)
);

comment on column parent.parent_id is 'Primary key of a parent row.';
comment on column parent.parent_name is 'Name of a parent. Business key of a parent row.';

Tests

Test SQL query

select c.owner || '.' || c.table_name || '.' || c.column_name as identifier,
       'Missing comment for column ' || c.column_name || ' on ' || lower(o.object_type) || ' ' || c.table_name || '.' as message
  from dba_col_comments c
  join dba_objects o
    on o.owner = c.owner
       and o.object_name = c.table_name
 where o.object_type in (#ObjectTypesWithPrimaryKey#)
   and c.owner in (#SchemaNames#)
   and c.table_name not like 'BIN$%' -- not a table in recycle bin
   and c.comments is null
   and table_name not like 'AQ$%'
   and not exists (
          select 1
           from dba_tables t
           where t.iot_type is not null
              and t.owner = c.owner
              and t.table_name = c.table_name
       )
  and not exists (
         select 1
          from dba_queue_tables t
          where t.owner = c.owner
             and t.queue_table = c.table_name
       )
 order by identifier

Test results

Identifier	Message	Migration
DBL_OWNER.PARENT.PARENT_ID	Missing comment for column PARENT_ID on table PARENT.	-
DBL_OWNER.PARENT.PARENT_NAME	Missing comment for column PARENT_NAME on table PARENT.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
SchemaNames	Comma separated list of database schemas owning the database objects of an application.	dbl_owner
ObjectTypesWithPrimaryKey	Comma separated list of object types that should be defined with a primary key.	table

References

related to Oracle SQL Language Reference - Comment
related to Trivadis Database Object Naming Conventions - Table
Comments on tables and columns are recommended.
related to Trivadis Database Object Naming Conventions - Column
Comments on columns are recommended.