How to join the COLUMNS and COMMENTS system tables

[Julie]

Hi,

I'd like to generate a report of all the columns in my database along with the associated column comments.

This is my attempt:

Code: Select all

SELECT cols.table_name
     , cols.ordinal_position
     , cols.column_name
     , coms.comment
  FROM columns cols
    LEFT JOIN comments coms
      ON coms.object_schema = cols.table_schema 
     AND coms.object_name = cols.table_name
     AND object_type = 'COLUMN'
 ORDER BY cols.table_name
        , cols.ordinal_position
        , cols.column_name;

I am confused as to what the OBJECT_ID is in the the COMMENTS table because there is no OBJECT_ID in the COLUMNS table, but I don't think I need that column.

Anyone see an issue with my query?

[JimKnicely]

Julie,

Your query will work.

You can also join the COMMENTS table to the TABLES table on the COMMENTS.OBJECT_ID column and the TABLE.TABLE_ID column, i.e.

Code: Select all

 select c.object_name, t.table_name from comments c join tables t on t.table_id = c.object_id;

[harryrundles]

Hi,

What about comments for the columns in the system tables that are in the v_monitor and v_catalog schemas? We can get a table comment for the tables in those schemas from the table_description column of the v_catalog.system_tables, but the v_catalaog.system_columns table doesn't have a comment. Weird.

[JimKnicely]

Julie,

Now that I think about it we can only add column comments to projections, not to tables! Therefore, you'll have to modify your query.

Example:

Code: Select all

dbadmin=> create table test (c1 int) unsegmented all nodes;
CREATE TABLE

I can't add a comment on a table column:

Code: Select all

dbadmin=> comment on column test.c1 is 'Test comment';
ROLLBACK 2639:  Column "test"."c1" does not exist as a projection column

I can only add comments to the table's projections...

Code: Select all

dbadmin=> select projection_name from projections where anchor_table_name = 'test';
 projection_name
-----------------
 test_node0001
 test_node0002
 test_node0003
(3 rows)

dbadmin=> comment on column test_node0001.c1 is 'Test comment';
COMMENT

Now I can find the projection column's comment in the comments table like this:

Code: Select all

dbadmin=> select c1.table_name,
dbadmin->        c1.ordinal_position,
dbadmin->        c1.column_name,
dbadmin->        c2.comment
dbadmin->   from columns c1
dbadmin->   join projections p
dbadmin->     on p.projection_schema = c1.table_schema
dbadmin->    and p.anchor_table_name = c1.table_name
dbadmin->   join comments c2
dbadmin->     on c2.object_schema = p.projection_schema
dbadmin->    and c2.object_name = p.projection_name || '.' || c1.column_name
dbadmin->    and c2.object_type = 'COLUMN'
dbadmin->  WHERE c1.table_name = 'test'
dbadmin->  order
dbadmin->    by c1.table_name,
dbadmin->        c1.ordinal_position;
 table_name | ordinal_position | column_name |   comment
------------+------------------+-------------+--------------
 test       |                1 | c1          | Test comment
(1 row)

Keep in mind that you can appear to have multiple comments for a single table column if there are more than one projection referencing that same table column!

Code: Select all

dbadmin=> comment on column test_node0002.c1 is 'Test comment - This is another one!';
COMMENT

Therefore, you'll probably want to include the projection name along with the table name:

Code: Select all

dbadmin=> select c1.table_name,
dbadmin->        p.projection_name,
dbadmin->        c1.ordinal_position,
dbadmin->        c1.column_name,
dbadmin->        c2.comment
dbadmin->   from columns c1
dbadmin->   join projections p
dbadmin->     on p.projection_schema = c1.table_schema
dbadmin->    and p.anchor_table_name = c1.table_name
dbadmin->   join comments c2
dbadmin->     on c2.object_schema = p.projection_schema
dbadmin->    and c2.object_name = p.projection_name || '.' || c1.column_name
dbadmin->    and c2.object_type = 'COLUMN'
dbadmin->  WHERE c1.table_name = 'test'
dbadmin->  order
dbadmin->    by c1.table_name,
dbadmin->       p.projection_name,
dbadmin->       c1.ordinal_position;
 table_name | projection_name | ordinal_position | column_name |               comment
------------+-----------------+------------------+-------------+-------------------------------------
 test       | test_node0001   |                1 | c1          | Test comment
 test       | test_node0002   |                1 | c1          | Test comment - This is another one!
(2 rows)

Hope this helps!

[Julie]

Wow, thanks for the great explanation! It is really a hassle that we can't create a sort of top level generic comment on a table's column.