Find Table and Column Name with table and column description

Many times we refer to TRM to get the description or know more about the columns in a table. The query below can be handy to know which tables use the column and get a small description about the table and the column.

Note: if there is a custom table created and not registered in oracle apps then those tables and columns will not be listed by the query below.

SELECT fa.application_id

, fa.application_short_name

, fat.application_name

, table_name

, column_name

, ft.description table_description

, fc.description column_description

FROM   fnd_tables ft

, fnd_columns fc

, fnd_application_tl fat

, fnd_application fa

WHERE  ft.table_id = fc.table_id

AND    fc.column_name = :column_name

AND    fat.application_id = ft.application_id

AND    fat.LANGUAGE = USERENV ('LANG')

AND    fa.application_id = fat.application_id