Oracle, How can I get tables where COMMENTS is not null and has specific column

Question

I want to get tables which have column 'CUST_ID' and their comments is not null.

To get tables with column_name 'CUST_ID'

SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='CUST_ID';

To get tables which comments is not null

SELECT TABLE_NAME, COMMENTS FROM user_tab_comments WHERE COMMENTS IS NOT NULL;

How can I get tables of which both comments is not null and have column_name 'CUST_ID'?

I have tried

SELECT count(*) FROM USER_TAB_COLUMNS, USER_TAB_COMMENTS 
      WHERE USER_TAB_COLUMNS.column_name='CUST_ID' 
        and USER_TAB_COMMENTS.COMMENTS IS NOT NULL;

But it doesn't work.

Answer 1

You could join both queries:

SELECT col.table_name, com.comment
FROM   user_tab_columns col
JOIN   user_tab_comments com ON col.table_name = com.table_name
WHERE  col.column_name = 'CUST_ID' AND 
       com.comments IS NOT NULL;