Toad World Blog

Reverse Engineer a SQL Query

Aug 24, 2009 11:52:00 AM by Daniel Norwood

The Query Builder in Toad for Data Analysts allows you to enter a SELECT statement in the Query tab and reverse engineer it into a visual representation on the Diagram tab. This can be very useful when migrating existing queries to Toad or when you just want to use the visual layout to better understand how a query is structured.

To begin, open a new Query Builder document and switch to the “Query” tab (lower left). Next, simply type or paste a SELECT statement into the editor. (Oracle users: make sure that the ANSI SQL button on the toolbar is turned OFF if you’re using the standard Oracle syntax to join your tables!)  Finally, click on the “Visualize SQL Statement” button to have Toad for Data Analysts draw a diagram of the objects and relations in your SQL query. Simply switch to the “Diagram” tab to see your query represented in a visual form. Now you can edit your query in either the Diagram tab or the Query tab, giving you great flexibility in editing your query.

If you have an existing SQL query file saved to your hard drive, here’s a shortcut: you can open it in the SQL Editor and right-click to send the SQL to the Query Builder where it will be reverse engineered automatically. This is an easy way to port your SQL queries into a common visual format for use as a collaborative resource.

Though Toad will reverse engineer most of the SQL syntax that you have, there are times when some of the SQL syntax may not be visually represented when it’s reversed engineered. When this occurs, a Global Where Clause object is added to the visual editor, containing the logic of that specific clause in your SQL statement. The Global Where Clause can still be edited by double-clicking on the object or by clicking the Global Where or Global Having buttons on the toolbar.

If you have errors in the SELECT statement, they will be flagged in the editor and added to the Output window. A dialog will also appear, informing you of the error.

 
Note: By default the Query Builder uses ANSI joins. Sending a non-ANSI join query to the Query Builder will put the WHERE condition into the Global Where Clause. If you change the join type to non-ANSI join, make a change, and press the “Visualize query” action it will build the query.

Tags: Toad Data Point Analysis

Daniel Norwood

Written by Daniel Norwood

Hi! I've been a product marketer, working with the Toad group. I've been working with our Toad products since 2001 and have even had the privilege to bring a few to market. When I'm not working, I'm usually either outside or with my family - preferably both! I currently live in sunny San Diego, CA with my wife and 3 little girls - and we're about to add a dog into the mix!