Query to find column not showing anything?

Question

I'm trying to find a table in a schema with a specific column name. So I used the following script, but it doesn't return anything:

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH 
from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like '%LATLONG%'
order by DATA_LENGTH desc;

On the other hand, a SELECT of table LATLONG_DETAIL will display a column called LATLONG_TYPE.

So why isn't the query displaying this table in its result?

All these queries are being run in the schema where table LATLONG_DETAIL resides.

Thanks.

You are mixing your views a bit. Try ALL_TABLES instead of USER_ALL_TABLES. The latter is restricting the view for tables owned by your current user. — unleashed, Mar 09 '17 at 22:33
Does `select table_name from user_tables where table_name = 'LATLONG_DETAIL'` return anything? If not, try with `all_tables` instead. If that doesn't work either, try `upper(table_name)` in the `where` clause. Report back what you find out. — , Mar 09 '17 at 23:50
@TheGrandDuke: Thanks. I did not know that USER_ALL_TABLES is limited to tables by the current user. ALL_TABLES worked. — fdkgfosfskjdlsjdlkfsf, Mar 10 '17 at 13:06

DCookie · Accepted Answer · 2017-03-09T22:30:34.003

You say you own the table LATLONG_DETAIL. The only other thing I can think of why your query isn't returning anything is that the column name is not in upper case. Does this query return anything?

SELECT a.table_name, column_name,DATA_TYPE,DATA_LENGTH 
  FROM all_tables u JOIN all_tab_columns a 
                      ON u.table_name = a.table_name
                     AND u.owner = a.owner
 WHERE UPPER(column_name) LIKE '%LATLONG%';

Query to find column not showing anything?

1 Answers1