How to get comments for table & column from oracle DB from its metadata?

Question

I am working on a Java Application. I have connected to an Oracle DB using JDBC Connection and fetched it metadata. I am fetch information like tables, columns, views, etc from its metadata.

Now I want to fetch Comments for tables and columns separately in the application from metadata. How can I fetch this details?

Answer 1

Now I want to fetch Comments for tables and columns separately in the application from metadata. How can I fetch this details?

For table comments, use [DBA|ALL|USER]_TAB_COMMENTS view.

Example:

SQL> SELECT table_name,
  2    comments
  3  FROM dba_tab_comments
  4  WHERE owner   ='OE'
  5  AND table_name='INVENTORIES';

TABLE_NAME  COMMENTS
----------- ---------------------------------------------------------------
INVENTORIES Tracks availability of products by product_it and warehouse_id.

For column comments, use [DBA|ALL|USER]_COL_COMMENTS view.

SQL> SELECT table_name,
  2    column_name,
  3    comments
  4  FROM dba_col_comments
  5  WHERE owner   ='OE'
  6  AND table_name='INVENTORIES';

TABLE_NAME  COLUMN_NAME          COMMENTS
----------- -------------------- ----------------------------------------------------------------------------
INVENTORIES PRODUCT_ID           Part of concatenated primary key, references product_information.product_id.
INVENTORIES WAREHOUSE_ID         Part of concatenated primary key, references warehouses.warehouse_id.
INVENTORIES QUANTITY_ON_HAND

Answer 2

SELECT *
  FROM user_tab_comments;

SELECT *
  FROM user_col_comments;

You can also use all|dba prefix instead of user.

Answer 3

Try dbms_metadata package. With it you can extract comments, grants and other things from db. SELECT DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT','TABLE_NAME','SCHEMA') FROM DUAL

Answer 4

if the other answers didn't work you should probably try:

1. Retrieving comments from tables and views

    SELECT * FROM ALL_TAB_COMMENTS 
   

2. Retrieving comments from Columns

    SELECT * FROM ALL_COL_COMMENTS
   

This worked for me in an Oracle-RDS (AWS).