SQL pretty printer-good SQL Format tool

Objective

For a long time did not write a blog, people become lazy a lot, should say originally also not very diligent. But today for this tool, you have to be diligent, there really is no free lunch.

The Query Designer and Toad for Oracle, which used SQL Server 2000 before, have the ability to format SQL statements, which is very handy, especially for those of me with mild obsessive-compulsive disorder. When I recently upgraded SQL Server Management Studio (SSMS) to version R2, there were no formatting features, and none of the previous 2005 and 2008, we didn't know what Microsoft was thinking. So just want to find a can format code in SSMS plug-in, search to search for a lot of people recommended SQL pretty Printer artifact, and then came to download the test, the use of the results are very satisfied with, the following talk about SQL pretty Printer add-in for Use it in SSMs.

Content

1, first download the installation program, here, the download is completed directly after the installation can be, there is nothing to say.

　　2. After the installation is complete, re-open ssms and you will see such as:

　　

3. Format Selected SQL

This function is mainly to format the SQL statement selected in the current window, only the SQL statement to be formatted before you can use this feature.

4. Fromat All SQL

This function is mainly to format all the SQL statements in the current window.

5. Effect:

Before formatting

　　　　

After formatting

　　　　

6. Rename Table and Rename Column

These two features are new additions to the 3.2.0, which are used primarily to rename table and column names in complex SQL statements, including nested subqueries, joins, parameters of function calls, parameters in stored procedures. Here is an example to see what the meaning of, I understand is similar to the use of regular expression matching, and then replace the table name and column name.

Use the following SQL statement to test:　　　　

SELECT Person.firstname,       Orders.name fromperson leftJOIN  Orders              on         =  orders.p_idORDERby   Person.firstname

　　　　

　　　　

After replacing the SQL statement, the table name does not change, only the column name changes, this function is very useful, encountered to modify the different tables of the same column name, very suitable.

　　　　

7, to C # (to VB. NET)

Converts an SQL statement into an output statement in the C # language. Using the SQL statement above, selecting the SQL statement you want to convert in SSMs and choosing to C #, you will see the following converted C # code, generating a string of SQL statements that can reduce the hassle of writing SQL statements in your code:

StringBuilder varname1 =NewStringBuilder (); varname1. Append ("SELECT persons.firstname, \ n"); varname1. Append ("orders.name \ n"); varname1. Append ("from Persons \ n"); varname1. Append ("Left JOIN Orders \ n"); varname1. Append ("On persons.p_id = orders.p_id \ n"); varname1. Append ("ORDER by Persons.firstname");

8. C # to SQL (vb.net to SQL)

Translating C # code into SQL statements, C # code seems to satisfy varname1. Append ("SQL statement"), can be converted to the corresponding SQL statements, on their website for half a day did not find the relevant description of the document, the test has been restricted use, good bitter force ah

9. SQL Formatter Options

This is the format of the option, open as shown, the specific features are not introduced, I did not look carefully, with the default settings should be enough.

　　　　

This article is purely personal understanding of this software, there is what is not the right place, also please treatise!

Reference

SQL Format Tool-sql pretty Printer

SQL Refactor:rename COLUMN and TABLE

SQL pretty Printer format SQL on the Fly | Gudu Software