Shulou(Shulou.com)06/01 Report--

SQL Compare is a tool for comparing and synchronizing SQL Server database structures. More than 150000 database administrators, developers, and testers now use it. SQL Compare automates the process of allocating the database when testing the local database, temporarily storing or activating the database on the remote server.

Inherit the database from another team? Changed team policy in the way SQL was formatted? When you develop it, what prevents you from well formatting the code for the entire database? The manual process may take longer than expected. This article demonstrates a simple three-step approach to reformatting an entire database in a single operation using SQL Compare and SQL Prompt.

First, generate the latest build scripts for the development database, including tables, code modules, and so on. You can use SQL Prompt to reformat the entire build script according to the team's standards. So far everything has been simple, but what's next?

The next task is to save these format changes to the development database. You need to design a migration script that executes each code module, object as a view, table-valued functions, scalar functions, triggers, and procedures to apply the new code format. In this way, the new source of these routines will remain in the database. However, due to complex inter-object references, this may not be as simple as building a script and changing the CREATE statement to ALTER. You may run into trouble when you try to run these ALTER commands.

For example, in addition to referencing in a view, another function, or a procedure, a function can also be bound to a calculated column, default constraint, or check constraint on a table column. If you try to change the function you are using in this way, you will receive an error:

Cannot ALTER''because it is being referenced by object''.

To avoid this, you must delete references and constraints before changing the function, and then replace them. If a function is referenced a lot, this makes it tricky to change the function. In addition, if WITH SCHEMABINDING is in use, the dependency is enforced. This means that all downstream routines must be changed to remove the schema binding before changing. All in all, it may become complicated.

After the migration script has been successfully executed, you also need to update the individual object scripts in the table and code module in source control to reflect the new formatted version of the database. Of course, the canonical source code in source control is the only place where you can save the CREATE TABLE statement format and all comments.

Fortunately, once you have generated the build script and formatted it using SQL Prompt, SQL Compare can do the rest of the hard work for you.

Step 1: generate and format the build script in SSMS

The first task is to generate the latest database build script. The easiest way is to use the SSMS Generate Scripts wizard in the sandbox, on the latest copy of the development database. In the SSMS object Explorer pane, right-click the development database of the source code you want to beautify. Then navigation task | generate script. To start the wizard.

We will generate everything for the new database and put it in a new query window.

The rest of the guide is self-evident. You will find the build script in the 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 the new database. These references will be enclosed by SSMS, so you should include these parentheses in the global search and replace the expressions on the database name, no matter where they are referenced. Therefore, if your database is named Snibbo and you want the build script to create NewSnibbo, find [Snibbo] and replace it with [NewSnibbo]. By doing so, the operation is more secure even if the "find" string is used elsewhere in comments, strings, or other symbols. Because unless you use the database name as the name of the database object, they will not have parentheses. Even if you call the database [Select] unwisely, changing it to [SelectCopy] won't hurt your query.

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

CREATE DATABASE

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

Step 2: use SQL Compare to automatically generate deployment scripts

The next task is to have SQL Compare create a deployment script that can change each code module in the development database to reflect the new format.

At this point, you have two options:

Save the reformatted build script to a file and compare it with the current database, or

Use a reformatted build script to create a new database and synchronize from it.

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 contain all the reformatted code in its module.

In this example, using the Sandbox development server, I have used a database called PhilsScripts, which contains a large number of diagnostic scripts. A build script has been generated, formatted, and used to create a new database on the server named BetterPhilsScripts. Then execute the SQL Compare and specify the newly formatted BetterPhilsScripts as the source and PhilsScripts as the destination.

We compare the two and find that SQL Compare has detected all changes made by SQL Prompt. Then we check the deployment script, and when we are satisfied, we will deploy the changes.

Step 3: update source control

With SQL Compare, you can save the database in the script directory or update the existing script directory of the source control system. This saves each object in its own file, but it can also maintain the build script. I use the PowerShell script to do this; when I change more than one object at a time in the development database, it periodically saves the object script, which I can then check through the local repository using Github Desktop or Gmaster.

Finishing

Therefore, through the combination of SQL Prompt and SQL Compare, you can not only reformat the entire database or schema routines, but also update a single source file or build script for source control. If you use the Migration method for database development, you can also save the SQL Compare synchronization script as source control as the basis for the migration script. All of this makes reformatting SQL code more enjoyable than just a chore.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.