ThatJeffSmith

Toad for Oracle Tip: Custom Queries

There are SO many little nuggets in Toad that you can take advantage of. To list all of them would be a daunting task. Here’s one that you may have overlooked, Custom Queries.

If you right-mouse-click on an object in the Schema Browser, you’ll see a list of operations you can perform. Take a second to look at the list for ‘Tables’. I have 36 operations available. The very last entry is ‘Custom Queries.’

So what is a custom query?

A custom query allows you to feed one or more selected object names in the Schema Browser and feed that as an input via a WHERE clause to some helpful queries. Toad ships with a few of these already written for you. The ‘custom’ comes into play when you create your own or tweak the defaults to match your needs.

A Quick Example
I need a quick inventory of constraints for a list of tables. I could select each table individually, go to the Right-Hand-Side (RHS) Constraints tab, and create a report, but that would be several steps multipled by the number of tables I needed to audit.

Instead, I can use a custom query to do this in only 3 steps!
1. Select your object(s)
2. Mouse-right-click | Custom Queries | Constraint Type Summary
3. Execute the query that is pasted into the editor

That’s it!

Look for 'Custom Queries' at the very bottom.

After the custom query is constructed in the Schema Browser, it is automatically pasted into the editor, ready for execution. Be sure to review and test the script to ensure it’s doing what you THINK it is supposed to be doing.

Less typing, more coding?!? Yes!

Now You are Ready to Create Your Own!
In the Schema Browser, just use the ‘Edit Custom Queries’ popup entry.

You will notice that you have a lot of control over the script. You can make it available for only certain versions of Oracle, substitute the ALL_ or the DBA_ data dictionary views, and if you REALLY trust your script, you can enable the ‘Auto Run’ option.