I frequently need to find a table in a particular SCHEMA, or even just anywhere in the database, that stores data of a specific datatype. This generally happens when a customer asks me, ‘Hey, does Toad support the ANYDATA|LONG RAW|CLOB|BLOB datatype?’

So I decide to find said table and just show them what the application can do with a real example. Unfortunately I usually need to go to the editor and run a query to find a table ‘manually.’ I do have a Named Query to help me do this:

select distinct owner, table_name from dba_tab_cols x
where X.DATA_TYPE = :Type
order by 1, 2;

Way 1 of 100 to do this in Toad

So case closed, right?

Not quite darling! I am your prototypical developer in at least one area: I am very, very, very lazy. So what’s a Toadie to do?

Customize the Schema Browser

Instead of just running a query in the editor, I’m going to code a custom filter in the browser for the ‘Tables’ page that allows me to just see tables that contain the type of data I want to play with. Then I can just immediately start clicking into the table right there in the browser. To do this I need to use the ‘Custom Filter’ feature.

Check and Check!

Here’s a list of steps:

  • Click on the filter dialog – the little funnel button
  • Click the ‘Custom Filter (Use View/Edit Query to adjust) checkbox
  • Click the ‘View/Edit Query Before Executing’ checkbox
  • Add an additional WHERE clause
  • Save the filter
  • Reopen the ‘View/Edit Query’ dialog whenever you need to change the datatype

After checking the ‘View/Edit Query’ and clicking on ‘OK’, we get the following dialog. The selected text is what I’ve added to help me with my search.

Add any WHERE clause to get the list of objects YOU want

Once you click ‘OK’, Toad will re-query the data dictionary and bring back the list of tables that have ‘CLOB’ datatypes. To make this re-usable, we now need to check re-click the filter dialog and Save the filter. Next time I’m looking for tables with TIMESTAMPS, I’ll just re-open the filter and change the query to probably something similar to … LIKE ‘TIMESTAMP%’

Program and reuse your filters!

Confession

It took me about an hour to figure this out. It was the ‘Custom Filter’ check that was eluding me. Only after getting some help from the developer was I able to figure it out. Even after supporting Toad for 10 years, I don’t know everything there is to know about Toad – and I never will. Just remember, if there’s something you want to do in Oracle, there’s probably a way to achieve it in your favorite IDE! Don’t be afraid to ask for help. That’s what we’re here for.