Get column name, data type, size and comments of Oracle table

Question

How can I get column name, data type, size and comments of table?

I tried

SELECT all_tab.column_name, all_tab.data_type, all_tab.data_length, col_com.COMMENTS 
    FROM all_tab_columns all_tab
        JOIN user_col_comments col_com ON all_tab.TABLE_NAME = col_com.TABLE_NAME
    WHERE all_tab.TABLE_NAME='MY_TABLE'

But it didn't work.

Answer 1

You need to add column name connection:

SELECT all_tab.column_name,
       all_tab.data_type,
       all_tab.data_length,
       (SELECT COMMENTS
          FROM user_col_comments t
         where t.TABLE_NAME = all_tab.TABLE_NAME
           and t.COLUMN_NAME = all_tab.column_name)
  FROM all_tab_columns all_tab
 WHERE all_tab.TABLE_NAME = 'MY_TABLE'