A continuing blog series about finding more of those “hidden” gems in Toad’s graphical interface.

“We do not follow maps to buried treasure, and ‘X’ never—ever—marks the spot. 70% of all archeology is done in the library.”
— Indiana Jones (from “Indiana Jones and the Last Crusade”)

If I Only Knew Then…

It wasn’t long ago that a customer (we’ll call him Brad) was sharing his screen with me to discuss how he’s using Toad for Oracle. During our session, I watched Brad execute a query in the Toad Editor. Moments later, he realized he had forgotten to include a few columns. So he modified the query and re-ran it. Not long afterward, he ran several more (similar) queries back-to-back. At some point, I became aware that each of Brad’s queries to this point were retrieving different sets of columns, but all from the same table.

I asked Brad, “Why don’t you simply use the table’s data grid tab in the Schema Browser to view any/all of your data columns?”  Brad explained that the table has over 300 columns, but he only needs to grab a few of them at a time. “I don’t have time to wade through hundreds of columns to find the ones I need.”

OK, hundreds of columns. Scrolling nightmare. I get it, so we moved on. Ah, but I wish I knew then what I know now. There is a better way. So, Brad (and those of us like him), if you’re reading, let’s talk about the “column chooser” icon.

… What I Know Now: the Column Chooser

The Column Chooser looks like this  (depending on your desktop color scheme) and appears in the upper left-hand corner of just about any data grid you encounter in Toad for Oracle (e.g. look to the immediate left of the first column header displayed in the grid.) This gem of an icon is embedded in dozens of grid displays in Toad—I stopped counting after seeing it in over 30 grids, and there’s way more!—so it’s not a rare jewel. It’s in plain sight! However, not many Toad users are aware of its existence because it appears so unobtrusively as part of the data grid frame.

The tool bar buttons bearing this icon image do what its tool tip says. Pressing this icon lists all columns that could be displayed and allows you to checkbox only the columns you want displayed in the data grid. Check out the screen snap below.

Why is this icon important?

The value of this tool bar button is obvious. It’s a fast convenient way to

• Provide you a quick inventory of any table’s (or query’s) columns (even for queries like SELECT * FROM… )
• Allow you to choose only the columns you wish to view.

Especially useful for tables with hundreds of columns like Brad’s. But there’s more convenience behind its face value.

Veteran Toad users should know that Toad does its filtering/sorting/grouping/aggregations/column selections/etc. on its data grids in memory as much as possible. So when Brad runs his full query once (and only once), then check-boxes a different set of columns, the Column Chooser grabs the data for his desired columns from Toad memory, not from the database server again. Convenient and quick for Brad, yeah, but Brad’s DBAs ARE HAPPY TOO: they won’t be seeing the same/similar queries eating up unnecessary data block reads on the database server.

But Wait, There’s More!

You can see from the previous screen snap that there’s a click-box to sort the column list alphabetically. OK, could be a quicker way to identify your columns of interest.

Oh, and there’s no need to keep re-choosing your fave columns every time you view table data grids in the Schema Browser. Toad remembers your last column selection for each of the tables you visit in the Schema Browser if your Oracle session remains connected in Toad. Super nice.

And yes, the Column Chooser works even in the data grids for database views and synonyms.

But (of course) there’s more. The screen snap below shows that right-clicking on the column list portion of the selection panel uncovers these “hidden” options:

• Check Selected/Unselected
  Now you don’t need 27 clicks to check-box your desired 27-column subset. Simply use a combination of Shift+Click and Ctl+Click with your mouse to quickly highlight your column set, then right-click to un/check your selection.
• Copy Checked Columns to Clipboard
  Ooh! What a fast way to build a SELECT query that lists only the columns you want (instead of typing them all out or issuing a “SELECT * FROM” query that returns unnecessary data.)
• Uncheck Columns With Matching Data in Selected Rows
  When you

1. Highlight a selection of rows in the data grid itself
2. Press the Column Selector
3. Right-click this option in the column selection list then columns that have duplicate values will get hidden from the grid display. The use case here is helpful when you don’t know which columns you want to hide until you see data values in those columns. This right-click option helps to avoid column “noise” when multiple columns contain duped data.

Stay Tuned

If you like finding treasured artifacts in Toad that make your life easier, then stay tuned for future posts in my “Raiders” series. If you’ve encountered a gem in Toad that has impacted the quality of your work life, we encourage you to share your experience by replying to this post or by starting a discussion.

Help your colleagues

If you think your colleagues would benefit from Gary’s productivity blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

More blogs from Gary

Interested in other Toad solution insights like this one? How about real-world accounts of how Toad has rocked someone’s world? Read Gary’s other blog posts.

Related blogs:

Now that you’ve got the data I want, what’s the best way to export it?
Want to use a visual query builder to bring back your data?
Why an Oracle ACE loves the Toad Editor