oracle - find Information about tables an columns

Question

I use the SQL Developer for various queries in our database schema. The tables are listed under the Objects container. There I can left-click and select view with then display information about the table under the Columns tab. The comments are especially important. Is there a way to query this directly via the Oracle Data dictionary? The describe or desc command doesn't work for me.

Answer 1

For tables, you can use USER_TABLES and USER_TAB_COMMENTS:

SELECT ut.table_name,
       ut.tablespace_name,
       utc.comments
FROM   user_tables ut
       LEFT OUTER JOIN user_tab_comments utc
       ON ut.table_name = utc.table_name
ORDER BY ut.table_name;

and, for columns, you can use USER_TAB_COLUMNS and USER_COL_COMMENTS:

SELECT utc.table_name,
       utc.column_name,
       utc.data_type,
       utc.data_length,
       utc.data_precision,
       utc.data_scale,
       utc.data_default,
       ucc.comments
FROM   user_tab_columns utc
       LEFT OUTER JOIN user_col_comments ucc
       ON utc.table_name = ucc.table_name AND utc.column_name = ucc.column_name
ORDER BY utc.table_name, utc.column_name, utc.column_id

Which, if you have the table:

CREATE TABLE table_name (
  id    NUMBER(10,0),
  value VARCHAR2(20)
);
COMMENT ON TABLE table_name IS 'A table for testing comments.';
COMMENT ON COLUMN table_name.id IS 'The identifier.';
COMMENT ON COLUMN table_name.value IS 'The value.';

Outputs, for the respective queries:

TABLE_NAME	TABLESPACE_NAME	COMMENTS
TABLE_NAME	USERS	A table for testing comments.

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	DATA_DEFAULT	COMMENTS
TABLE_NAME	ID	NUMBER	22	10	0	null	The identifier.
TABLE_NAME	VALUE	VARCHAR2	20	null	null	null	The value.

fiddle

Answer 2

Query e.g.

• USER_TABLES (for list of tables)
• USER_TAB_COLUMNS (for columns in these tables)
• USER_OBJECTS (for any objects in your schema)
• USER_TAB_COMMENTS (for comments related to tables and views)
• USER_COL_COMMENTS (for comments related to columns in these tables and views)

---

You didn't specify which database you use; on Oracle 11g, you can query the dictionary view which contains list of all system views and short description. That's (in my opinion) a cool option:

For example: which views contain "comment"?

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> select table_name, comments
  2  from dictionary
  3  where lower(comments) like '%comment%';

TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------------------------------------
USER_COL_COMMENTS              Comments on columns of user's tables and views
USER_INDEXTYPE_COMMENTS        Comments for user-defined indextypes
USER_MVIEW_COMMENTS            Comments on materialized views owned by the user
USER_OPERATOR_COMMENTS         Comments for user-defined operators
USER_TAB_COMMENTS              Comments on the tables and views owned by the user
ALL_COL_COMMENTS               Comments on columns of accessible tables and views
ALL_EDITION_COMMENTS           Describes comments on all editions in the database
ALL_INDEXTYPE_COMMENTS         Comments for user-defined indextypes
ALL_MVIEW_COMMENTS             Comments on materialized views accessible to the user
ALL_OPERATOR_COMMENTS          Comments for user-defined operators
ALL_TAB_COMMENTS               Comments on tables and views accessible to the user

11 rows selected.

SQL>

---

The same works on e.g. 18c as well, but it does not work on 21cXE as dictionary has the whole comments column empty (don't know why):

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL> select count(*) from dictionary where comments is not null;

  COUNT(*)
----------
         0

SQL>