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

You may also like

  • Article

    How to build multiple database versions from the same source: pre-deploy migration scripts

    This is the third post in a three-part series that explains how to maintain a single declarative source of truth in version control for a SQL Server database, which can be deployed to multiple environments, despite the fact that some environments have known customizations. fl In part 1, I explained how to manage objects that

  • Article

    SQL Prompt Code Analysis: A Hint is Used (PE004-7)

    “Because the SQL Server query optimizer typically selects the best execution plan, we recommend that hints be used only as a last resort by experienced developers and database administrators” — Microsoft SQL Server Documentation Really, there should be no discussion about this, because the above warning, in the documentation, summarizes it so well. However, I

  • Article

    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 unstable changes to a version control system (VCS). However, I do need a lightweight way to keep track of my changes.

  • Article

    Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)

    It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as

  • University

    Take the SQL Comparison Tools course

    This course takes you from installation all the way up to getting the most out of the industry standard tools for SQL Schema & Data comparison.

    Whether you need to deploy changes or explore the differences between your environments, through this course you'll learn how SQL Compare and SQL Data Compare can help you do the job quickly, simply, and with zero errors.

  • Forums

    SQL Compare Forum

    Forum for users of SQL Compare schema synchronization utility