Control of DB structure change

Preamble

A couple of years ago, my staff and I encountered the problem of maintaining a large number of similar databases at remote branches. Sometimes they were removed thousands of kilometers, and the volumes were calculated in gigabytes - which did not allow to use any server mirroring. The situation was complicated by minor differences, i.e. 95% of the business logic of the branches coincided, but there were still 5% that strongly interfered and there was no question of any automatic processing of the group of branches.

For the data, the application was finally written to synchronize the “common” structure, which actually interested the central office. However, the key point was precisely business logic: table structures, triggers, stored procedures. Those. when the logic was updated in the center - the same changes needed to be made in all branches, and not to break the individual characteristics of each branch.

The second point that had to be streamlined was the work of the team with a large database (more than 100 tables, tens of thousands of SQL rows in procedures and triggers). You can imagine a situation where one programmer solves the problem and does not specifically touch the neighboring logic or calculation. To track this is problematic.

Formulation of the problem

The basic requirements for the functionality were simple:

• the ability to compare two databases;
• automatic script generation for converting one base to another;
• creating a "snapshot" of the base
• search for objects in business logic (dependency search)

All of this in our case should have worked on SQLServer 2005+.
')

Product

For several weeks of part-time employment, a utility was written that could help us with all these misfortunes. The program is written in C # .NET 3.5.

View and analyze the structure of the database or snapshot.

When browsing, it is especially convenient to “pull out” pieces of scripts from procedures, functions, triggers ... But sometimes creating tables and indexes is necessary.


Search by base structure.
Here is more interesting. Entering the name of the table will show you not only this table, but all references to it, references to stored procedures, functions, and triggers. In the same way, you can look for, for example, domains (we at one moment decided to abandon them and translate all types into standard ones).

Comparison of database structures and / or snapshot of the database structure

The main window of the program. In the upper part, the connection with the database or a previously saved snapshot of the structure is indicated. In the lower part of the window, the full script for structure conversion is displayed.


Here you can see the script to change a specific object, in this case, a table. Full conversion script, all external links and indexes.


Comparison of two versions of the object. Looks like SVN, right? :) You can see new / deleted columns, indexes, links, etc. Changes in the stored procedure code will also be visible - the most important.

Epilogue

The product turned out to be very popular, as with us and with familiar programmers. From there we learned another important feature of the product: control the actions of other developers. :)
At the moment, the program works only with SQLServer. But in the code, the implementation follows the principle of drivers, i.e. other databases are possible over the next year. Naturally you should not understand this product as a 100% analysis of changes in the structure of the database. It is rather the control of the framework and business logic, while the administrative elements remain on the administrators' conscience.

The product is completely free, and should be used only for the benefit of :)
Of course you can't sell it.

Download program: archive

UPD: The file is updated, a bug with the inability to copy the script. Thank you seele