-
标题
Formatting option mockups and examples -
解决办法
Applies to
ApexSQL RefactorSummary
This article describes the ways in which you can format SQL code using ApexSQL Refactor features and options. ApexSQL Refactor is a free add-in with over 200 formatting options and nearly 15 code refactors.From this link you can download the most recent version of ApexSQL Refactor and play along trough the article.
Table of content
- Formatting
- Capitalization
- Comments
- Statements
- Lists
- Expressions
- Arithmetic
- Comparison
- Logical
- Joins
- Flow control
- Obfustacion
- Shortcuts
Formatting
In the Formatting tab, can be set indentation by using spaces or tabs. Aligns identifiers in the data statements like SELECT, INSERT or UPDATE statement by using Smart indent option. Additionally, space around assignment operators, before and after commas can be added. Using options under the Empty lines section empty lines can be removed or added before/after comments, before each statement, several empty lines can be changed into one empty line, etc.:
Back to topSpacing
Add spaces around assigment operators:
UPDATE Production.Product SET ListPrice=(ListPrice - 100);
UPDATE Production.Product SET ListPrice = (ListPrice - 100);
Add spaces before commas:
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate ) VALUES (N'FT2, N'Square Feet, '20080923' ); GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode , Name , ModifiedDate ) VALUES (N'FT2', N'Square Feet', '20080923' ); GO
Empty lines
Remove empty lines:
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
Keep empty lines before/after comments:
/* comment */
/* comment */-- comment1 -- comment2 -- comment3
-- comment1 -- comment2 -- comment3
-- comment1
-- comment1Keep empty lines before each statement :
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
Change several empty lines into one empty line:
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
SELECT * FROM le1 SELECT * FROM le2 SELECT * FROM le3
Miscellaneous
Remove unnecessary brackets:
UPDATE Production.Product SET ListPrice = (ListPrice - 100)
UPDATE Production.Product SET ListPrice = ListPrice - 100
Always use statement terminator:
UPDATE Production.Product SET ListPrice = ListPrice - 100
UPDATE Production.Product SET ListPrice = ListPrice - 100;
Enclose identifiers in brackets:
UPDATE Production.Product SET ListPrice = ListPrice – 100;
UPDATE Production.Product SET [ListPrice] = [ListPrice] - 100;
Capitalization
Under the Capitalization tab can be set capitalization style for keywords, data types, identifiers, system function and variables:
Back to topDECLARE @Initials varchar(5); DECLARE @FirstName varchar(25); DECLARE @LastName varchar(25); SET @FirstName = 'Katie'; SET @Lastname = 'Melua'; SELECT @Initials = SUBSTRING(@FirstName, 1, 1) + SUBSTRING(@Lastname, 1, 1);
DECLARE @initials VARCHAR(5); DECLARE @firstname VARCHAR(25); DECLARE @lastname VARCHAR(25); SET @firstname = 'Katie'; SET @lastname = 'Melua'; SELECT @initials = SUBSTRING(@firstname, 1, 1) + SUBSTRING(@lastname, 1, 1);
Comments
In the Comments tab, empty line before or after block comments can be added. Also, border above or below block comments can be set using any separator such (*, -, +, /). Additionally, all comments type can be change into block/inline comments or simply remove all comments from the code: Back to topBlock comments
Insert empty line before/after block comments
SELECT * FROM AWB /* comment comment */ WHERE A=B
SELECT * FROM AWB /* comment comment */ WHERE A=B
Add border above/below block comments using <->
SELECT * FROM AWB /* comment comment */ WHERE A=B
SELECT * FROM AWB /*------- comment comment -------*/ WHERE A=B
Change all comments to block/line comments
SELECT * FROM AWB /* comment comment */ WHERE A=B
SELECT * FROM AWB --comment --comment WHERE A=B
SELECT * FROM AWB --comment --comment WHERE A=B
SELECT * FROM AWB /*comment comment*/ WHERE A=B
Remove all block/line comments
SELECT * FROM AWB /*comment comment*/ WHERE A=B
SELECT * FROM AWB WHERE A=B
SELECT * FROM AWB --comment --comment WHERE A=B
SELECT * FROM AWB WHERE A=B
Statements
Under the Statements tab, a lot of options can be set for data statements, nested selections, object definitions, aliases, etc. In the Data statements section, only statements that are longer than specified number of characters can be formatted. FROM, WHERE and SET clause can be moved to in a new line, align with the keyword, or indent by specified number of spaces. Parentheses in the nested selections can be placed into a new line, align with the keyword, or indented. The AS keyword can be aligned with the keyword or intended:
Back to topData statements
Move FROM clause to a new line
SELECT * FROM Customers WHERE CustomerID = 1;
SELECT * FROM Customers WHERE CustomerID = 1;
Align with keyword
SELECT * FROM Customers WHERE CustomerID = 1;
SELECT * FROM Customers WHERE CustomerID = 1;
Move WHERE clause to a new line
Align with FROM
SELECT * FROM Customers WHERE CustomerID = 1;
SELECT * FROM Customers WHERE CustomerID = 1;
Move SET clause to a new line
Align with keyword
UPDATE Sales.SalesOrderHeader SET SalesPersonID = 1;
UPDATE Sales.SalesOrderHeader SET SalesPersonID = 1;
Nested selects
Format parentheses placement
Move opening parentheses to a new line
Align with keyword
SELECT * FROM (SELECT * FROM AWBud)
SELECT * FROM (SELECT * FROM AWBud)
Indent 3 spaces
SELECT * FROM (SELECT * FROM AWBud)
SELECT * FROM (SELECT * FROM AWBud)
Move closing parentheses to a new line
Align to the end of previous line
SELECT * FROM (SELECT * FROM AWBud)
SELECT * FROM (SELECT * FROM AWBud )
Align with keyword
SELECT * FROM (SELECT * FROM AWBud)
SELECT * FROM (SELECT * FROM AWBud )
Move code with parentheses to a new line
Place at same position
SELECT * FROM (SELECT * FROM AWBud)
SELECT * FROM ( SELECT * FROM AWBud)
Aliases
Align all alias names
SELECT alpha a, col1 c bta AS b, gammaone g
SELECT alpha a, col1 c bta AS b, gammaone g
Always use AS for aliases in SELECT statements
SELECT alpha a, col1 c bta AS b, gammaone g
SELECT alpha AS a, col1 AS c bta AS b, gammaone AS g
Place AS on new line
Indent
SELECT column1 AS a, column2 AS b FROM spt_values AS c, table1 AS d
SELECT column1 AS a, column2 AS b FROM spt_values AS c, table1 AS d
Align with keyword
SELECT * FROM Table1 AS a WHERE Datag=Sept
SELECT * FROM Table1 AS a WHERE Datag=Sept
Other
Always use INTO in INSERT statements
INSERT HResources (ID, Office, FName) VALUES (1,200,Charles)
INSERT INTO HResources (ID, Office, FName) VALUES (1,200,Charles)
Place body on new line
CREATE
