David Atkinson

21 September 2016

1 Comment

• SQL Prompt
• SQL Prompt

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:

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 …

… into one that looks more like this:

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:

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.

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:

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!