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.
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:
1 |
Cannot ALTER '<name of referenced object>' because it is being referenced by object '<Name of referencing object>'. |
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 …
1 |
CREATE DATABASE <nameOfNewDatabase> |
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:
- Save the reformatted build script to a file, and compare that to your current database, or
- 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.