Product articles SQL Prompt SQL Formatting and Styles
How to format a set of SQL scripts…

21 September 2016

1 Comment

21 September 2016

1 Comment

How to format a set of SQL scripts using SQL Prompt

Any database developer or DBA who spends much of their working week staring at SQL code quickly becomes set in their ways. They like to see the code laid out in a very particular way. They will struggle to look at, let alone digest and understand, code formatted in a ‘foreign’ style. It’s also rare to find two database developers who agree in detail on exactly how SQL code should be formatted.

When asked to review someone else’s code, or perhaps to do some performance tuning on a set of stored procedures, it will slow progress down considerably if the developer needs to reformat each object script manually.

Formatting a single script

A tool such as SQL Prompt can help speed up this process. It comes with a couple of pre-installed code formatting templates and simply by selecting Format SQL from the menu, or using the Ctrl+K, Ctrl+Y keyboard shortcut, any script can be auto-formatted according to the selected template. It also offers a range of options for inserting semi-colons, applying specific casing standards, adding or removing square brackets and more, as you can see:

SQL Prompt helps with SQL formatting in many ways

I won’t cover the options in detail here, but check out some of these short videos and it will give you the idea. In short, with a few key strokes, a developer can use SQL Prompt to transform a script that looks like this …

SQL formatting can be ugly ...

… into one that looks more like this:

Or SQL formatting can make SQL easy to work with.

The latest version of the tool (7.3, currently in beta) also allows users to define multiple custom templates to exert precise control over code layout.

Formatting all scripts for a set of objects

If you’ve inherited a new database and are performing a code review, or there is some other reason it might be useful to format many objects in a database, scripting out each of them individually and applying the formatting changes would be impractical for all but the smallest databases.

One way to speed this up is to script the required objects to a single query window using SSMS’s Generate Scripts wizard. Right-click on the database, navigate Tasks | Generate Scripts, then select the specific objects you wish to format. In this example, all views and stored procedures in the AdventureWorks2014 database have been selected:

DA-SQL-Prompt-4

In theory, you could extend this technique to include objects such as functions. However, you’ll need to watch out for dependencies and references, since functions are often referenced in table constraints, computed columns, and triggers (more on this shortly).

On the next screen, choose to save the script to a new query window. By default, SSMS will script out the selected objects as CREATE TO statements, but clicking the Advanced button opens up the Advanced Scripting Options, and the Script DROP and CREATE option allows you to CREATE (or DROP) all the objects, or drop the object if it already exists, and then create it.

DA-SQL-Prompt-5

The ‘drop the object if it exists, then create it’ option might seem the obvious one to choose, but the problem with this technique is that dropping the existing object and recreating it loses any permissions, as well as any extended properties associated with that object. In this case, you simply want to ALTER these objects, to reformat them, retaining all associated permissions and so on. Therefore, choose the CREATE TO scripting option.

Having completed the wizard and generated the script, you need to replace all the CREATE statements with ALTER. You can do this in a single pass using regex-based ‘Quick Replace’, with the following regular expression:

CREATE[^:a]+{(PROCEDURE|VIEW)}

It searches for strings starting with CREATE, followed by any number of spaces, followed by PROCEDURE or VIEW. For all matches, you simply want to replace CREATE with ALTER and leave the remainder of the string untouched, using the following regular expression:

ALTER \1

Remember to check the Use: box on the Quick Replace (Ctrl-F) screen, and select Regular expressions in the dropdown:

DA-SQL-Prompt-6

With all of the objects scripted out as ALTER statements, in a single query window, simply choose SQL Prompt’s Format SQL option, or use the Ctrl+K, Ctrl+Y shortcut, in order to format the script in the desired manner.

The final step is to execute the script. You’ll firstly need to remove, or comment out, the sp_addextendedproperty execution, which you’ll find at the end of the script. You’ll also encounter problems if altering an object with dependencies. For example, if you had included functions in the above example, executing the final script would result in the following dependency error, since the AccountNumber column in the Customer table is a computed column that calls the ufnLeadingZeros function:

Msg 3729, Level 16, State 3, Procedure ufnLeadingZeros, Line 374
Cannot ALTER 'dbo.ufnLeadingZeros' because it is being referenced by object 'Customer'.

You can either omit objects with dependencies, or disable the reference, make the change then re-enable it. However, you’re less likely to encounter problems with stored procedures and views, and once you’ve executed the script, as you review each individual object, it will be formatted exactly as you like it!

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

  • We have few thousand procedures and functions, each in a separate file. How can I go about formatting all of them in one go instead of formatting one at a time?

You may also like

  • Article

    Improving legacy code using SQL Prompt code analysis

    SQL Prompt code analysis, introduced in v9.2, provides a quick and easy way to assess the overall health and quality of your SQL scripts. It will help the team prevent technical debt from entering the code base, to remove it from existing code during code reviews, and to ensure that the released code conforms to the team's defined coding standards.

  • Article

    Cleaning up common T-SQL coding issues with SQL Prompt

    Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. This article covers five harmful problems that I see regularly in T-SQL code, and shows

  • Article

    When to use the SELECT…INTO statement (PE003)

    SELECT…INTO is a useful shortcut for development work, especially for creating temporary tables. However, it no longer has a clear performance advantage and should be avoided in production code. It is better to use a CREATE TABLE statement, where you can specify constraints and datatypes in advance, making it less likely that inconsistencies will sneak into the data.

  • Article

    Why your Development team needs SQL Prompt

    While everyone knows SQL Prompt for its code completion and IntelliSense features, a lot of its extra value comes from features that allow the development team to standardize coding practices and drive up code quality.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly