These Aren't the Tables You're Looking For

14 October 2008

Sometimes you have a large database schema and a lot of tables that you're not sure what they contain. You do know, however, the nature of the data'ific needle that you're trying to find in your schema'ific schema. Oracle provides some cool dictionary tables that might help you in your quest.

Sometimes you have a large database schema and a lot of tables that you're not sure what they contain. You do know, however, the nature of the data'ific needle that you're trying to find in your schema'ific schema. Oracle provides some cool dictionary tables that might help you in your quest.

Try to find what you're looking for by table name:

select *
from   dba_tab_cols
where  owner = 'SCHEMA_NAME'
and    table_name like '%SWEET%';

Try to find what you're looking for by column name:

select *
from   dba_tab_cols
where  owner = 'SCHEMA_NAME'
and    column_name like '%COOLNESS%';