Oracle PL/SQL: Copying Column Comments From One View To Another

This blog post gives a piece of simple SQL that will allow you to automatically copy the comments from one database view to another. In the case of this example we are copying the column comments from a standard oracle view to a materialised view which has the same name except with "_MV" suffix.


The PL/SQL block is;

declare
  v_ViewName varchar2(30) := 'XXX';
begin
  for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
                      from all_col_comments acc
                     where acc.OWNER = USER
                       and acc.TABLE_NAME = v_NoetixViewName) loop
    execute immediate
      'comment on column ' || v_ViewName || '_MV.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
  end loop;
end;

To use this code in your system replace XXX with the name of the view you want to copy from and where is says "...  v_ViewName || '_MV ..." you should replace this with the naming convention you are using for the view you want to copy to.

For example if you source view is called SOURCE and your target view is called TARGET then the SQL would become;

declare
  v_ViewName varchar2(30) := 'SOURCE';
begin
  for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
                      from all_col_comments acc
                     where acc.OWNER = USER
                       and acc.TABLE_NAME = v_NoetixViewName) loop
    execute immediate
      'comment on column TARGET.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
  end loop;
end;

I've found this useful when creating new materialised views based on existing NoetixViews to copy across all the comments from the NoetixView to the Materialised view (rather than having to write the comments in myself).

Hope this helps!