New release: SQL Pretty Printer Version 3.2.8

October 14th, 2011
  • Introduce 14 new format options in this version and there are more than 90 format options in SQL Pretty Printer.
  • Fix a bug that menu and toolbar disappeared in vs2010 after first installation.
  • About 2 times faster when format some big SQL scripts.
  • Fix a bug that can’t format join clause correctly in this sql: 
    SELECT
  *
FROM
  S1
  natural JOIN W3
  LEFT OUTER JOIN S2
    ON S1.S = S2.S
  • Improve format result if there is a comment after WHERE keyword directly.
  • Lots of minor bugs fixed.

Posted in release, sql pretty printer | No Comments »

How to add syntax colored and well formatted SQL in your wordpress blog

October 4th, 2011

People always post SQL code into blogs and forums need a tool to help them to prepare dull monochrome, badly-formatted SQL code for HTML-based publication in a nice clean style.

SQL Pretty Printer is able to output HTML in both <code> and <pre> blocks in a convenient way with code like this:

	<div><span style="font-family: Courier New; font-size: 10pt;">
	<span style="color: blue; ">WITH</span> <span style="color: maroon; ">mycte</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span>
	<br/>     <span style="color: blue; ">AS</span> <span style="color: maroon; ">(</span><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">=</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: red; ">'hello'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'a'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'b'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span><span style="color: maroon; ">)</span>
	<br/><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span>
	<br/><span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/><span style="color: blue; ">ORDER</span>  <span style="color: blue; ">BY</span> <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span>
	<br/>          <span style="color: maroon; ">x</span><span style="color: silver; ">;</span> 
	</span></div>

You can copy and embedded above HTML code into your blog to get a colored SQL like this:

WITH mycte(x)
     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘a’)
         FROM   mycte
         WHERE  Len(x) < 10
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘b’)
         FROM   mycte
         WHERE  Len(x) < 10)
SELECT x
FROM   mycte
ORDER  BY Len(x),
          x; 

But in wordpress, the output will be looked like this that add additional lines.

WITH mycte(x)

     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘a’)

         FROM   mycte

         WHERE  Len(x) < 10

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘b’)

         FROM   mycte

         WHERE  Len(x) < 10)

SELECT x

FROM   mycte

ORDER  BY Len(x),

          x; 

It seems that editor of wordpress add an additional <br/> tag at the end of each line. this issue can be resolved by changing

function wpautop($pee, $br = 1) {

to

function wpautop($pee, $br = 0) {

in wp-includes/formatting.php

Posted in formatting, sql, wordpress | No Comments »

case(Capitalization) user defined identifier in SQL statement

October 4th, 2011

SQL Pretty Printer provides the ability to capitalize user defined identifier such as stored procedure name start with sp_ in SQL Server.

name: case_prefix_identitfer

setting path: capitalisation -> Prefix_ident

name: identifier_prefix_list

setting path: capitalisation -> Identifier prefix list

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_prefix_identitfer to uppercase and identifier_prefix_list to sp_,

EXECUTE SP_EXECUTESQL
  @query,
  n’@age int’,
  @age = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case variable name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of variable name in SQL statement, this feature helps you to find out variable in SQL statement quickly.

name: case_variable_name, default is no change.

setting path: capitalisation -> Variable

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_variable_name to upper case,

EXECUTE Sp_executesql
  @QUERY,
  n’@age int’,
  @AGE = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case alias name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of alias name in SQL statement, this feature helps you to find out alias in SQL statement quickly.

name: case_alias_name, default is no change.

setting path: capitalisation -> Alias

SELECT departmentname,
       Min(salary) AS min_salary
FROM   employees
GROUP  BY departmentname 

After setting case_alias_name to upper case,

SELECT departmentname,
       Min(salary) AS MIN_SALARY
FROM   employees
GROUP  BY departmentname 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case column name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of column name in SQL statement, this feature helps you to find out columns in SQL statement quickly.

name: case_column_name, default is no change.

setting path: capitalisation -> Column

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_column_name to upper case,

SELECT DEPARTMENT_ID,
       Min(SALARY)
FROM   employees
GROUP  BY DEPARTMENT_ID 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case table name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of table name in SQL statement, this feature helps you to find out tables in SQL statement quickly.

name: case_table_name, default is no change.

setting path: capitalisation -> Table

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_table_name to upper case,

SELECT department_id,
       Min(salary)
FROM   EMPLOYEES
GROUP  BY department_id 

Posted in format option, sql pretty printer, v238 | No Comments »

Case(capitalization) options used when formatting SQL statements

October 4th, 2011

There are 5 case options available while formatting SQL tokens in SQL Pretty Printer.

1. Uppercase(All caps): All letters are capital letters.

2. Lowercase: All letters are lower case.

3. InitCap: Only the first letter of the whole word capitalized.

4. No Change: Case of all letters in the word not changed.

5. InitCapEachWord: Also known also CamelCase or Pascal case, First letter of each word is capitalized.

TCaseOption = (coUppercase,coLowercase,coInitCap,coNoChange,coInitCapEachWord);

Posted in format option, sql pretty printer | No Comments »

Case (Capitalization) in SQL statement

September 29th, 2011

When format SQL query, capitalization refers to the way in which SQL token should or should not be capitalized in your code. For example, some developers prefer to make all reserved keys uppercase, others prefer lowercase, and some mix and match. It’s all a matter of preference. When determining what strategies to implement with regard to case, we should take into account the following considerations:

1. SQL token should be categorized in terms of keywords, identifier, data types, variables and constants. Identifier includes schema name, table name, column name, alias name, function/stored procedure name, parameter and etc. Some SQL tokens may be treated as a specific category such identifier start with sp_ in SQL Server.

2. Case rules can be upppercase, lowercase, InitCapEachWord(camelCase), InitCap(capitalize first letter only) and no change.

Once you have those considerations in mind, then you can define your own capitalization strategies.

Let’s look at some examples that demonstrate different capitalization strategies.

In the first statement, all SQL reserved words are uppercase, all identifier such as tables and columns are lowercase, but the first letter of function name was capitalized(InitCap).

SELECT p1.productmodelid
FROM   production.product AS p1
GROUP  BY p1.productmodelid
HAVING Max(p1.listprice) >= ALL (SELECT Avg(p2.listprice)
                                 FROM   production.product AS p2
                                 WHERE  p1.productmodelid = p2.productmodelid); 

In the next example, all the reserved words and identifer are lowercase, but the table name and alias name are uppercase.

select P1.productmodelid
from   production.PRODUCT as P1
group  by P1.productmodelid
having Max(P1.listprice) >= all (select Avg(P2.listprice)
                                 from   production.PRODUCT as P2
                                 where  P1.productmodelid = P2.productmodelid); 

Here’s one other example. This time, the keywords are uppercase, the data types lowercase, identifier(such as dbo) lowercase, the table and column are camel case:

CREATE TABLE dbo.EmployeePhoto
  (
     EmployeeId      int NOT NULL PRIMARY KEY,
     Photo           varbinary(Max) NULL,
     MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL UNIQUE DEFAULT Newid()
  ); 

The key to a capitalization strategy should be readability. There’s no real right or wrong, as long as the standards are applied consistently throughout the organization.

SQL formatting tool can help you do this job precisely and quickly

Some conventional sql formatting tools use ad hoc string processing methods to implement the formatting process, so it can’t distinguish table and column name from other identifiers.

SQL Pretty Printer parse the SQL query according to the syntax of various database SQL dialect. So it can easily turn table name(product) into uppercase while make column name(productmodelid) camelCase like this.

select ProductModelId
from   production.PRODUCT 

Please note that schema name(production) was keep unchanged.

SQL Pretty Printer also has the ability to apply case rule to a specific identifier category, identifier start with sp_ was set to uppercase in next SQL statement:

execute SP_EXECUTESQL
  @QUERY,
  N’@Age int’,
  @AGE = 25 

Here is a screenshot of SQL Pretty Printer’s capitalization format options.

SQL Pretty Printer capitalization format options

SQL Pretty Printer capitalization format options

Posted in formatting, sql | No Comments »

sql formatter option: how to place a comma in the list of parameter in execute statement

September 29th, 2011

This format option controls where to put comma in the list of parameters of SQL Server execute statement. a line break can be placed after comma, or before comma

name: exec_parameters_comma_option, default is put line break after comma

setting path: Execute -> parameter comma option

EXEC Spr_store_note
  @NoteId = 0,
  @TableTypeId = @TABLE_CUSTOMER,
  @RecordId = @CustomerId,
  @Descr = @Comments 

After set exec_parameters_comma_option to “before comma”, SQL will be formatted like this:

EXEC Spr_store_note
  @NoteId = 0
  ,@TableTypeId = @TABLE_CUSTOMER
  ,@RecordId = @CustomerId
  ,@Descr = @Comments 

After set exec_parameters_comma_option to “before comma with a space”, SQL will be formatted like this:

EXEC Spr_store_note
  @NoteId = 0
  , @TableTypeId = @TABLE_CUSTOMER
  , @RecordId = @CustomerId
  , @Descr = @Comments 

Posted in format option, sql pretty printer, v238 | No Comments »

« Older Entries

Gudu Software 2002-2011  Privacy Policy  Terms of Use  About Us