How to customize SQL formatting profiles

Applies to
ApexSQL Refactor

Summary
This article describes how to use ApexSQL Refactor formatting profiles and is a guidance through the process of managing profiles.

Description

The built-in formatting profiles

ApexSQL Refactor has four built–in formatting profiles – ApexSQL, Compact, Extended and MSDN SQL BOL. All the formatting profiles have the same formatting options, but setting these options is what sets them apart. The same SQL code, formatted with different formatting profiles, will look different.

An example of the unformatted SQL code:

CREATE PROCEDURE spInsertOrUpdateProduct

-- Input parameters --

@ProductNumber NVARCHAR(25),
@ListPrice     MONEY
AS
IF EXISTS
(
SELECT *
FROM Production.Product
WHERE ProductNumber = @ProductNumber
AND ListPrice > 1000
)
UPDATE Production.Product
SET
ListPrice = (ListPrice - 100)
WHERE ProductNumber = @ProductNumber;
ELSE
INSERT INTO Production.Product
(ProductNumber,
ListPrice
)
SELECT @ProductNumber,
@ListPrice;
GO

The following are examples of how the SQL code looks like when formatted with one of the built-in formatting profiles:

ApexSQL:

CREATE PROCEDURE spInsertOrUpdateProduct

-- Input parameters --

@ProductNumber NVARCHAR(25),
@ListPrice     MONEY
AS
     IF EXISTS
     (
         SELECT *
         FROM Production.Product
         WHERE ProductNumber = @ProductNumber
               AND ListPrice > 1000
     )
         UPDATE Production.Product
           SET
               ListPrice = (ListPrice - 100)
         WHERE ProductNumber = @ProductNumber;
         ELSE
     INSERT INTO Production.Product
     (ProductNumber,
      ListPrice
     )
     SELECT @ProductNumber,
            @ListPrice;
GO

Compact:

This formatting profile has set all indentations to be ‘’0’’ (zero) spaces, all the options for adding additional spaces are unchecked and furthermore all the empty lines are removed from the query. Using this formatting profile will cause the SQL code to look denser.

CREATE PROCEDURE spInsertOrUpdateProduct
-- Input parameters --
  @ProductNumber NVARCHAR(25),
  @ListPrice     MONEY
AS
IF EXISTS
(
  SELECT *
  FROM Production.Product
  WHERE ProductNumber=@ProductNumber
        AND ListPrice>1000
)
    UPDATE Production.Product
    SET
        ListPrice=ListPrice-100
    WHERE ProductNumber=@ProductNumber;
  ELSE
INSERT INTO Production.Product
(ProductNumber,
 ListPrice)
SELECT @ProductNumber,
       @ListPrice;
GO

Extended:

This formatting profile is opposite to the Compact formatting profile. The Extended or “verbose” formatting profile expands SQL code with additional empty lines (before and after every statement, before and after every comment). Under this formatting profile, every formatting option regarding additional space around operators (arithmetic, logical, assignment), commas (before and after) and parentheses (before and after) are checked. The indentations are set to be much bigger than in all the other built-in formatting profiles.

This formatting profile has preset formatting options for adding BEGIN and END keywords in IF statements and stored procedures, if they do not exist already.

CREATE PROCEDURE spInsertOrUpdateProduct

-- Input parameters --

                 @ProductNumber NVARCHAR(25) ,
                 @ListPrice     MONEY
AS
     BEGIN
         IF EXISTS (
         SELECT *
         FROM Production.Product
         WHERE ProductNumber = @ProductNumber
               AND
               ListPrice > 1000
                   )
             BEGIN
                 UPDATE Production.Product
                        SET ListPrice = ( ListPrice - 100 )
                 WHERE ProductNumber = @ProductNumber;
         END
             ELSE
             BEGIN
                 INSERT INTO Production.Product ( ProductNumber , ListPrice
                                                )
                 SELECT @ProductNumber , @ListPrice;
         END;
     END;
GO

MSDN SQL:

This formatting profile mimics formatting rules used in the MSDN resource site. The formatting options for indentation, adding space around operators, adding BEGIN and END keywords in IF statements and stored procedures are set slightly differently from the other three built-in formatting profiles that ApexSQL Refactor has to offer.

CREATE PROCEDURE spInsertOrUpdateProduct

-- Input parameters --

		  @ProductNumber nvarchar(25), @ListPrice money
AS
BEGIN
	IF EXISTS
	(
		SELECT *
		FROM Production.Product
		WHERE ProductNumber = @ProductNumber AND 
			  ListPrice > 1000
	)
	BEGIN
		UPDATE Production.Product
		  SET ListPrice = ( ListPrice - 100 )
		WHERE ProductNumber = @ProductNumber;
	END
	ELSE
	BEGIN
		INSERT INTO Production.Product( ProductNumber, ListPrice )

		SELECT @ProductNumber, @ListPrice;
	END;
END;
GO

Built-in formatting profiles cannot be changed in any way. On attempt to change formatting settings for any of the profile, the following message appears:

Trying to edit a built-in profile name, author or description for any of these formatting profiles, by clicking on the Edit button, the following message will raise:

Clicking the Delete button, in order to delete any of these formatting profiles, will end with the following message:

Besides the built-in formatting profiles, ApexSQL Refactor offers the possibility of creating a new formatting profile.

Creating the user-defined formatting profiles

To create a new formatting profile as a copy of one of the existing built-in formatting profiles, from the formatting profiles drop-down list, select formatting profile which should be copied. Then click the Copy button.

The Copy profile window shows the name, the author and the description of the copied built-in profile. Any of the information can be edited or left as they are. For saving this created formatting profile, just click the OK button and the created profile will appear on the formatting profiles list.

Quick tip: The name of the formatting profile in the Copy profile window will be automatically generated as

For creating a new formatting profile from the scratch, click the New button.

This action will open the Create profile window. The Author field already contains information of the last author who created the new formatting profile. When creating a new formatting profile for the first time, the author will be ApexSQL. The author can be changed, simply by entering a new author. The last author will be remembered for the next time when a new formatting profile is created:

The Description field can contain additional information which is important for that specific formatting profile, like how the formatting options are set, for which script or statement, date when the formatting profile is created, etc. To create a new formatting profile, beside the author, the only other field that must contain information is the Name. There are not character restrictions when it comes to creating the name for a new formatting profile. After this information is entered, click the OK button and a new formatting profile will appear on the formatting profile list:

Quick tip: For creating a new formatting profile, the ApexSQL formatting profile is used as the default template

Editing user-defined formatting profiles

For changing/modifying any of the new created formatting profile information, click the Edit button. In the Edit profile window, any information, regarding the Name, Author and Description, can be changed and saved again by clicking the OK button:

Deleting user-defined formatting profiles

To delete a custom formatting profile, select the formatting profile that needs to be deleted and click the Delete button. The next message will appear:

To delete the selected formatting profile, click the Yes button. This action will remove the deleted formatting profile from the formatting profile list, but to save these changes click the Save button of the Options window.

The ‘’Set as active’’ option

The Set as active option is very useful in managing the formatting profiles. Open the Options window. Select the desired formatting profile from the formatting profile list, and click on the Set as active option. To save the settings, click the Save button of the Options window. To format the SQL code with the selected formatting profile, just click Format SQL button in the ApexSQL Refactor toolbar. To format the SQL code with any other formatting profile, go to the ApexSQL Refactor main menu and, from the Format SQL by profile sub-menu, choose formatting profile:

Quick tip: Qualifying object name and Wildcard expansion options are also related to the formatting profile that has been set as active. Using any of these features will also format the SQL code by using the active formatting profile

Importing/Exporting formatting profiles

To export formatting options settings, under any formatting profile, click the Export button. In the Export profiles window, one or more formatting profile can be chosen, whose settings will be saved.

Quick tip: With the Export profiles feature only, the user-define formatting profiles can be exported

The Import feature provides the possibility to import existing profiles.

Open the Options window and click the Import button. Choose the formatting settings XML file to import. Click the Open button and the Import profiles window will appear.

In this window, all the previously exported formatting profiles, discovered in this file, will be pre-checked for import as well. After checking all the formatting profiles to be imported, click the OK button and the imported formatting profiles will now appear on the formatting profiles list. To save this change, click the Save button of the Options window:

Q: Is it possible to have more than one user-defined formatting profile?

Yes, it is, there is no limit on the number of the user-defined formatting profiles.

Q: What happens if the already existing formatting profile is imported again?

In this case, the newer imported formatting profile will have “Copy” appended to its name.

Q: Will a new installation or upgrade of ApexSQL Refactor delete user-defined formatting profiles?

A: No, installation, or upgrade to a newer version won’t affect custom changes.

Q: Where is information about the formatting profiles saved?

A: By default, all information about formatting profiles are saved in the following location:

C:\Users\<current_user>\AppData\Local\ApexSQL\ApexSQLRefactor\Options.xml