25 January 2018
25 January 2018

How to reformat a database in one operation

Guest post

This is a guest post from Phil Factor.  
Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect.

This article will demonstrate a simple 3-step approach to reformatting a whole database to your standard, in a single operation, using SQL Compare and SQL Prompt.

What’s the problem?

You start by generating the latest build script for the development database, including the tables, code modules and all. You can reformat that entire build script, according to your team’s standard, using SQL Prompt. All simple enough so far, but then what?

The next task is to save these formatting changes to the development database. You need to devise a migration script that, when executed, alters each of the code modules, objects as views, table-valued functions, scalar functions, triggers and procedures, to apply the new code formatting. That way, the new source of these routines is preserved in the database. However, due to complex inter-object references, this isn’t likely to be as simple as going through the build script and changing CREATE statements to ALTER. You’d probably hit trouble when you tried to run those ALTER commands.

For example, as well as being referenced in a view, another function or, a procedure, a function can also be bound to a computed column, a DEFAULT constraint or a CHECK constraint, on columns of a table. If you try to alter a function being used this way, you’ll get the error:

To avoid this, you would have to remove the references and constraints before altering the function, and then replace them. If a function is referred to a lot, this makes altering a function rather tricky. Also, if WITH SCHEMABINDING is being used, dependencies will be enforced. This means that you must change all down-stream routines to remove schema binding, before you alter it. All in all, it can get complicated.

Having successfully executed your migration script, you then have the additional task of updating the individual object scripts in source control, for both the tables and code modules, to reflect the new, formatted version of the database. Of course, the canonical source in source control is the only place where formatting of CREATE TABLE statements can be preserved, along with all the comments.

Fortunately, once you’ve generated your build script, and formatted it with SQL Prompt, SQL Compare can do the rest of this hard work for you.

Step 1: Generate and format a build script in SSMS

The first task is to generate the latest database build script. The easiest way is to is to use the SSMS Generate Scripts wizard, on an up-to-date copy of the development database, in a sandbox. In the SSMS Object Explorer pane, right-click on the development database whose source code that you want to beautify. Then navigate Tasks | Generate Scripts…to bring up the wizard.

We’ll just generate everything for the new database, and place it in a new query window.

The rest of the wizard is self-evident. You will end up with a build script in a new query window.

We don’t want to rebuild our development database so, in the build script, we need to change all references to the database to the name of a new database. These references will be square-bracketed by SSMS, so you should include these brackets in the global search and replace expressions on the database name, wherever it is referenced. So, if your database is called Snibbo and you want the build script to create NewSnibbo, then find [Snibbo] and replace with [NewSnibbo]. By doing this, the operation is considerably safer, even if the ‘find’ string is used elsewhere in comments, strings or other symbols. because unless you’ve used the database name as the name of a database object, they will be without the brackets. Even if you are unwise enough to call your database [Select], it is unlikely to harm your queries if you change it to [SelectCopy].

The CREATE DATABASE statement at the beginning of the script needs to be changed to the simplest …

You should then be able to reformat this script easily, by clicking on the menu item SQL Prompt > Format SQL. (or Ctrl K Ctrl Y). As you might have chosen to remove unnecessary square brackets, it is best to do the change of database name first.

Step 2: Auto-generate a deployment script with SQL Compare

The next task is to get SQL Compare to create a deployment script that alters each of the code modules in the development database to reflect the new formatting.

You have two options at this point:

  1. Save the reformatted build script to a file, and compare that to your current database, or
  2. Use the reformatted build script to create a new database and do the synchronization from that.

I’ll demonstrate the second technique, so we need to use the build script to temporarily create a new version of the database that will have all the reformatted code in its modules.

In this example, using a Sandbox development server, I’ve taken a database I’ve written called PhilsScripts with a whole lot of diagnostic scripts in it. I’ve generated a build script, formatted it, and used that build script to create a new database on the same server called BetterPhilsScripts. I then execute SQL Compare and specify the newly-formatted BetterPhilsScripts as the source, and PhilsScripts as the target.

We compare the two and find that SQL Compare has detected all the changes that SQL Prompt has made. We then check through the deployment script and when we are happy we deploy the changes.

Step 3: Update source control

With SQL Compare, you can then save the database in a script directory or update an existing script directory for your source control system. This saves each object in its own file but can also maintain a build script. I use a PowerShell script to do this; it saves off object scripts routinely, when I’m changing many objects at once on my development database, and then I can check through the local repository, using Github Desktop or Gmaster.

Tidying up

So, with a combination of SQL Prompt and SQL Compare, it is possible to not only reformat the routines of an entire database or schema, but also to update the individual source files or build script for source control as well. If you are using a ‘migrations’ approach to database development, you can also save your SQL Compare synchronization script to source control, as the basis of a migration script. It all makes reformatting your SQL code into more of a pleasure than a chore.

Guest post

This is a guest post from Phil Factor.  
Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement

Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain o...

Also in Product learning

Choosing Between Table Variables and Temporary Tables

People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that b...

Also in SQL Compare

SQL Compare Snapshots: a lightweight database version control and rollback mechanism

I'm a big fan of using SQL Compare during proof-of-concept (POC) development. During the very early stages, I'm often unsure of the value of my current coding efforts and am not ready to commit unstab...

Also in SQL Prompt

Avoid use of the MONEY and SMALLMONEY datatypes

The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify moneta...