CODEWITHSUNDEEP
oracle-databasemetadatadatabase-metadata
Mehaboob Khan
Mehaboob Khan

Reputation: 363

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

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?

Upvotes: 6

Views: 36077

Answers (4)

Juan Camilo Gallego Gallon
Juan Camilo Gallego Gallon

Reputation: 21

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).

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

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

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

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

Upvotes: 11

hinotf
hinotf

Reputation: 1138

SELECT *
  FROM user_tab_comments;

SELECT *
  FROM user_col_comments;

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

Upvotes: 3

Related Questions