The other day, while working on a project I needed to find all the tables in a schema with a particular column name. Say for example, I wanted to list all the tables having a column name ‘empid’. Following is the Oracle query to do just the same.
select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
and column_name like ‘empid%’
order by DATA_LENGTH desc;
This query will also help you to find the Datatype and length of the column name. So go explore..
Also check my post on sys_context function here. This helps to find information about the Oracle environment. Very useful.