_SQL_Formatting

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

The SQL->Format SQL menu contains four operations for formatting SQL statements.

Format Buffer formats the complete editor content and Format Current formats the current SQL (at cursor position).

The formatting is done according to the settings defined in the Tool Properties dialog, in the SQL Editor/SQL Formatting category under the General tab. There are many things you can configure. After making some changes, press Apply and format again to see the result.

Here is an example of an SQL statement before formatting:

select
CompanyName, ContactName, Address,
City, Country, PostalCode from
Northwind.dbo.Customers OuterC
where CustomerID in (select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD
join Northwind.dbo.Orders O on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc)
order by Region

And here is the same statement after formatting has been applied with default settings:

SELECT
    CompanyName,
    ContactName,
    Address,
    City,
    Country,
    PostalCode
FROM
    Northwind.dbo.Customers OuterC
WHERE
    CustomerID in
    (
    SELECT
        top 2 InnerC.CustomerId 
    FROM
        Northwind.dbo.[ORDER Details] OD 
    JOIN
        Northwind.dbo.Orders O 
        ON
        OD.OrderId = O.OrderID 
    JOIN
        Northwind.dbo.Customers InnerC 
        ON
        O.CustomerID = InnerC.CustomerId 
    WHERE
        Region = OuterC.Region 
    GROUP BY
        Region, 
        InnerC.CustomerId 
    ORDER BY
        SUM(UnitPrice * Quantity * (1-Discount)) DESC) 
ORDER BY
    Region

Copy Formatted and Paste Formatted are powerful tools for copying SQL statements between programs written in languages like Java, C#, PHP, VB, etc. and the SQL Editor. Both operations display a dialog where you can adjust some of the formatting options, most importantly the Target SQL option and the SQL is Between option. Target SQL can be set to a number of common programming language formats.

For instance, to copy an SQL statement and paste it as Java code for adding it to a Java StringBuffer:

Select the statement,
Choose SQL->Format SQL->Copy Formatted,
Set Target SQL to Java StringBuffer,
Click Format to place the formatted statement on the system clipboard,
Paste it into your Java code.

To copy a statement wrapped in code from a program:

Select the code containing an SQL statement in your program,
Copy it to the system clipboard,
Choose SQL->Format SQL->Paste Formatted,
Check SQL is Between and enter the character enclosing the SQL statement in the code,
Click Format to extract the SQL statement and paste the formatted SQL in the editor.

Users Guide

_SQL_Formatting