Introduction

I started working for my current employer five years ago. In the beginning, every time we created a new database object, we generated a script and then send
it by mail to the person responsible for implementation. Two years ago, we started to use a Subversion server for versioning of source code and scripts.
It is a much more better way to store and manage projects and databases (object scripts). Sometimes it happens that someone forgets to store all the objects
on Subversion, especially when the project is huge, and therefore I decided to create a tool which will allow me (and other colleagues) to compare database schemas.
Now I only have to compare the local development database with other referential databases and I can see which objects are different or missing and then I can create the scripts for them.

Background

When I decided to create a tool for database schema comparison, I started investigation on what this tool should provide and how to generate and compare scripts.
I found out two ways to generate scripts. The first is to use the SQL Server System Views. This approach is the fastest but needs more programming
effort. The second way is to use the scripting capabilities of Server Management Objects. How to script objects using SMO has been described in my previous article. I decided to use this
way (but in the future, I want to create a custom scripting library which will use SQL Server System Views). As I mentioned earlier, this is not as efficient
as using System Views, but you can still use some performance tuning techniques to improve it.

In this project, the first step is to get the URNs of the objects you want to script. The URNs you can be got from the properties of the DataBase object.
The following database objects are scripted by this tool:

Application Roles

Database Roles

Defaults

FullText Catalogs

FullText StopLists - only for SQL Server 2008

User Defined Table Types - only for SQL Server 2008

Message Types

Partition Functions

Partition Schemes

Plan Guides

Remote Service Bindings

Rules

Schemas

Service Contracts

Queues

Routes

Assemblies

Stored Procedures

DDL Triggers

View

Synonyms

Tables

Users

User Defined Aggregates

User Defined Types

User Defined Functions

User Defined Data Types

Xml Schema Collections

Indexes

I have created a class ScriptedObject which uses a Hashtable hsObject for storing the URNs and other properties (Name, Type, Schema, ObjectDefinition).
All objects in hsObject have a unique key which is a combination of the Type and Name properties. I will use hsObjects later in the script generation
and especially for storing the generated script for every object. After we get all the URNs, we can start scripting.

For script generation, I have used the Script() method of the Scripter object which takes the URNs array of the objects you want to script.
The Scripter object has a property Options where you can set the scripting options (whether you want script collation, filegroup …).
The Script() method returns a StringCollection which contains the scripts for all objects. The problem is that you actually don't know which item
in this collection belongs to which item in the Hastable hsObject that I mentioned earlier. I have found that one of the properties of the ScriptingOptions
object allows you to add script headers. I have used these script headers to distinguish between scripts and to split these scripts.

The program loops though StringCollection and tests whether an item in this StringCollection contains a script header. If yes, the name and type of the object
can be extracted from the script header. These two properties uniquely identify objects in the hasthable hsObject. Every string in StringCollection
which comes after the script header belongs to an object identified earlier by type and name until another header comes or until the string begins with
ALTER, GRAND, REVOKE, DENY (in this case, you must extract the name of the object from the script and append it the appropriate object’s script).
The “ALTER” case is not so difficult to handle, because from the script you can extract the type and name of the object and then append the script to the appropriate object’s script.
The problem occurs with the “REVOKE”, “DENY”, and “GRAND” cases. In these cases, you can’t get the type of the object (I will fix these cases later).

Using the code

This solution contains two projects. The first project is called DBCompare and is the main project of this solution which serves for script generation
and comparison. The second one called DefferenceEngine is a base class for retrieving the differences between two strings (in this case, two scripts).
More about that class can be found here. The DBCompare project consists of six forms:

Login - serves for creating a database server connection.

MDIMain - serves as the container for all other forms.

ObjectCompre - serves for comparison of selected database objects.

ObjectFetch - in this screen, all the scripts are generated and then are passed to the ObjectCompare screen for comparison.

ObjectCompare form

The ObjectCompare form is the main part of this project. It displays database objects and shows the differences between them. When this form is shown, it automatically
shows the login screen, where you can input the login parameters for the database servers and set the scripting options. When all of these parameters are set, they are passed
to the ObjectCompare screen. When this information is retrieved, the ObjectFetch screen is shown and the scripting begins.

This screen consists of three parts. The first part is a left panel where you can select what database objects you want to compare (for example, when you select
only the Tables checkbox, in the list, you will see only tables; this is a good feature when you don't want to compare all types of database objects).
The second part is a list of all database objects. This list is divided into four parts:

Objects that exist only in DB1

Objects that exist only in DB2

Objects that exist in both databases and are different

Objects that exist in both databases and are identical

When you click on one of the items in the last third part, scripts of the selected objects are compared and displayed.

All objects are stored in the DataTable dbObjects which has six columns:

ResultSet - it specifies the group into which the object belogns (objects that exist only in DB1 [1], objects that exist only in DB2 [2], ...).

Name - name of the database object.

Type - type of the database object.

Schema - schema into which the database object belongs (not all objects belong to a database schema).

ObjectDefinition1 - if two databases have objects with the same name and type but with different definition,
ObjectDefinition1 stores the definition of the database object of the source database.

ObjectDefinition2 - if two databases have objects with the same name and type but with different definition,
ObjectDefinition2 stores the definition of the database object of the target database. If the object exists only in one of the databases,
this property is blank and the definition of the object is stored in ObjectDefinition1.

The list of displayed objects is refreshed by the RefreshObjectsList function.

Login form

The login form serves for creating the connections to the databases which you can compare. It consists of two tabs. In the first tab called Login Options, you can input
the SQL Server database name and credentials. The first two ComboBox objects (cboServer1 and cboServer2) load
the SQL Server local and remote instances.

Then you can select from two authentication modes (Windows authentication and SQL Server authentication). When you select the second one, you must input a user name and password.
After this step, you can select a databases from the Database ComboBox objects (cboDatabase1 and cboDatabse2). When you click
on one of the Database comboboxes, the list of databases for the current server is automatically populated using RefreshDatabaseList. This function is overloaded.
The first version accepts two parameters: cbo, which specifies which combobox to populate, and server, which specifies the server. The second version
of this function accepts four parameters. The first two are the same as in the previous version and the second two parameters are login and password.
The first version is used when Windows authentication is used and the second one is used when SQL Server authentication is used.

In the Scripting Options class, you can set the scripting options for the Scripter object which generates the scripts.

Here is a list of the supported options:

Clustered Indexes

Gets or sets a Boolean property value that specifies whether statements that define clustered indexes are included in the generated script.

DRI Checks

Gets or sets the Boolean property value that specifies whether column specific dependency relationships as defined in check constraints
with enforced declarative referential integrity are included in the script.

DRI Clustered

Gets or sets the Boolean property value that specifies whether dependency relationships defined in clustered indexes
with enforced declarative referential integrity are included in the script.

DRI Defaults

Gets or sets the Boolean property value that specifies whether dependency relationships defined in defaults with
enforced declarative referential integrity are included in the script.

DRIForeign Keys

Gets or sets the Boolean property value that specifies whether dependency relationships defined in foreign keys
with enforced declarative referential integrity are included in the script.

DRI Indexes

Gets or sets the Boolean property value that specifies whether PRIMARY KEY constraints that use a unique index to implement
the declarative referential integrity are included in the script.

DRI NonClustered

Gets or sets the Boolean property value that specifies whether dependency relationships that are defined in non-clustered
indexes that have enforced declarative referential integrity are included in the script.

DRI Primary Keys

Gets or sets the Boolean property value that specifies whether dependency relationships that are defined in primary keys which
have enforced declarative referential integrity are included in the script.

Dri Unique Keys

Gets or sets the Boolean property value that specifies whether dependency relationships that are defined in unique keys
which have enforced declarative referential integrity are included in the script.

DRI With No Check

Gets or sets the Boolean property value that specifies whether the no check statement is included in the script.

Extended Properties

Gets or sets the Boolean property value that specifies whether extended object properties are included in the generated script.

FullText Catalogs

Gets or sets the Boolean property value that specifies whether full-text catalogs are included in the generated script.

FullText Indexes

Gets or sets the Boolean property value that specifies whether full-text indexes are included in the generated script.

FullText Stop Lists

Gets or sets the Boolean property value that specifies whether full-text stop lists are included in the generated script.

Indexes

Gets or sets the Boolean property value that specifies whether indexes are included in the generated script.

No Assemblies

Gets or sets the Boolean property value that specifies whether assemblies are included in the generated script.

No identities

Gets or sets the Boolean property value that specifies whether definitions of identity property seed and increment are included in the generated script.

No File Stream

Gets or sets an object that specifies whether to include the FILESTREAM_ON clause when you create VarBinaryMax columns in the generated script.

No FileGroup

Gets or sets the Boolean property value that specifies whether to include the 'ON <filegroup>' clause in the generated script.

No Collation

Gets or sets the Boolean property value that specifies whether to include the Collation clause in the generated script.

No File Stream Column

Gets or sets an object that specifies whether to include the FILESTREAM_ON clause when you create VarBinaryMax columns in the generated script.

No Index Partition Schemes

Gets or sets the Boolean property value that specifies whether partition schemes for indexes are included in the generated script.

No Table Partition Schemes

Gets or sets the Boolean property value that specifies whether partition schemes for tables are included in the generated script.

Permissions

Coming soon.

Triggers

Gets or sets the Boolean property value that specifies whether to include the definition for triggers in the generated script.

NonClustered Indexes

Gets or sets the Boolean property value that specifies whether non-clustered indexes are included in the generated script.

XML Indexes

Gets or sets the Boolean property value that specifies whether XML indexes are included in the generated script.

After setting all of the parameters (serves, databases, scripting options), you can click on the Login button. When all input parameters are correct,
they are send to the ObjectCompare screen. All scripting options are retrieved by the GetScriptiongOptions function of the login screen.

ObjectFetch form

ObjectFetch is the heart of script generation. In this form, all of the objects are scripted and the scripts are stored in a DataTable object which
is passed to the ObjectCompare screen where it is displayed. Script generation starts with determining the database objects. In this phase, the URNs of objects are collected.

Future development

This program can be used for script generation SMO. This approach is not so fast, but after some performance tuning, you can speed up
the script generation. Pre-fetching of objects rapidly increases performance. I plan to create a custom class for scripting of database objects which will
use SQL Server Dynamic Management Views. This will be much more faster than scripting via SMO but it will require more programming effort.