25 January 2018
25 January 2018

How to reformat a database in one operation

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? Manually, the process can take longer than you expect. Phil Factor demonstrates a simple 3-step approach to reformatting a whole database , in a single operation, using SQL Compare and SQL Prompt.

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.

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 Apply Non-Standard SQL Formatting Using SQL Prompt

    SQL Prompt not only will format your code exactly as you want, but will also help you switch quickly to an alternative style, or to apply exceptions to certain parts of a SQL script, where your preferred style isn't what's required.

  • Article

    SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause

    Writing GROUP BY clauses is a very common, but tedious, activity for a SQL programmer. SQL Prompt will fill it in for you, with all non-aggregated columns returned by the query.

  • Article

    Refactoring a Batch or Object with SQL Prompt

    Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three.

  • Article

    Source controlling your database permissions

    How to use a combination of database roles, along with rules, filters and post deployment scripts in SQL Source Control, to add the required users to the correct role, for each database.

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Compare Forum

    Forum for users of SQL Compare schema synchronization utility