SQL Server Database Comparison Tool

• Download demo - 1.00 MB
• Download source code - 1.13 MB

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.
• ScriptView - in this screen, the synchronized script is shown.

and a class:

public class ScriptedObject
{
    public string Name="";
    public string Schema="";
    public string Type="";
    public DateTime DateLastModified;
    public string ObjectDefinition="";
    public Urn Urn;  
}

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.

private void RefreshObjectsList()
{
    bool isFirstConditionAdded = false;
    StringBuilder condition =new StringBuilder();

    for (int i = 0; i < tableLayoutPanel1.Controls.Count;i++ )
    {
        Control ctrl = tableLayoutPanel1.Controls[i];
        if (ctrl.GetType() == typeof(CheckBox))
        {
            if (((CheckBox)ctrl).Checked)
            {
                if (!isFirstConditionAdded)
                {
                    condition.Append("Type='" + ctrl.Tag.ToString() + "'");
                    isFirstConditionAdded = true;
                }
                else
                {
                    condition.Append(" OR Type='" + ctrl.Tag.ToString() + "'");
                }
            }
        }
    }

    DataView dwObjects = new DataView(dbOjects, condition.ToString(), 
                             "Type,Name", DataViewRowState.CurrentRows);
    lwDatabaseObjects.Items.Clear();
    lwDatabaseObjects.Groups.Clear();
            
    lwDatabaseObjects.Groups.Add("1", "Objects that exist only in " + Database1 + " ");
    lwDatabaseObjects.Groups.Add("2", "Objects that exist only in " + Database2 + " ");
    lwDatabaseObjects.Groups.Add("3", "Objects that exist in both databases and are identical");
    lwDatabaseObjects.Groups.Add("4", "Objects that exist in both databases and are different");
            
    foreach (DataRowView dr in dwObjects)
    {
        int imgIndex = 0;
        switch (dr["Type"].ToString())
        {
            case "STOREDPROCEDURE":
                imgIndex = 0;
                break;
            case "TABLE":
                imgIndex = 1;
                break;
            case "USERDEFINEDFUNCTION":
                imgIndex = 2;
                break;
            case "VIEW":
                imgIndex = 3;
                break;
            case "APPLICATIONROLE":
                imgIndex = 4;
                break;
            case "DATABASEROLE":
                imgIndex = 5;
                break;
            case "DEFAULT":
                imgIndex = 6;
                break;
            case "FULLTEXTCATALOG":
                imgIndex = 7;
                break;
            case "FULLTEXTSTOPLIST":
                imgIndex = 7;
                break;
            case "USERDEFINEDTABLETYPE":
                imgIndex = 1;
                break;
            case "MESSAGETYPE":
                imgIndex = 8;
                break;
            case "PARTITIONFUNCTION":
                imgIndex = 9;
                break;
            case "PARTITIONSCHEME":
                imgIndex = 10;
                break;
            case "PLANGUIDE":
                imgIndex = 12;
                break;
            case "REMOTESERVICEBINDING":
                imgIndex = 15;
                break;
            case "RULE":
                imgIndex = 13;
                break;
            case "SCHEMA":
                imgIndex = 14;
                break;
            case "SERVICECONTRACT":
                imgIndex = 16;
                break;
            case "SERVICEQUEUE":
                imgIndex = 17;
                break;
            case "SERVICEROUTE":
                imgIndex = 18;
                break;
            case "SQLASSEMBLY":
                imgIndex = 19;
                break;
            case "DDLTRIGGER":
                imgIndex = 20;
                break;
            case "SYNONYM":
                imgIndex = 21;
                break;
            case "USER":
                imgIndex = 22;
                break;
            case "USERDEFINEDAGGREGATE":
                imgIndex = 23;
                break;
            case "USERDEFINEDTYPE":
                imgIndex = 24;
                break;
            case "USERDEFINEDDATATYPE":
                imgIndex = 25;
                break;
            case "XMLSCHEMACOLLECTION":
                imgIndex = 26;
                break;
            case "TRIGGER":
                imgIndex = 27;
                break;
            case "INDEX":
                imgIndex = 28;
                break;
        }

        switch (dr["ResultSet"].ToString())
        {
            case "1":
                this.lwDatabaseObjects.Items.Add(new ListViewItem(new string[] { 
                    dr["Type"].ToString(),dr["Schema"].ToString(),
                    dr["Name"].ToString() }, imgIndex, lwDatabaseObjects.Groups[0]));
                break;
            case "2":
                this.lwDatabaseObjects.Items.Add(new ListViewItem(new string[] { 
                    dr["Type"].ToString(),dr["Schema"].ToString(),
                    dr["Name"].ToString() }, imgIndex, lwDatabaseObjects.Groups[1]));
                break;
            case "3":
                this.lwDatabaseObjects.Items.Add(new ListViewItem(new string[] { 
                    dr["Type"].ToString(),dr["Schema"].ToString(),
                    dr["Name"].ToString() }, imgIndex, lwDatabaseObjects.Groups[2]));
                break;
            case "4":
                this.lwDatabaseObjects.Items.Add(new ListViewItem(new string[] { 
                    dr["Type"].ToString(),dr["Schema"].ToString(),
                    dr["Name"].ToString() }, imgIndex, lwDatabaseObjects.Groups[3]));
                break;
        }
    }
}

This function is called when you click the Refresh button.

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.

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
    foreach (DataRow dr in dt.Rows)
    {
        cboServer1.Items.Add(dr["Name"].ToString());
        cboServer2.Items.Add(dr["Name"].ToString());                   
    }
}

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.

private void RefreshDatabaseList(ComboBox cbo, string server, string login, string password)
{
    try
    {
        cbo.Items.Clear();
        ServerConnection conn = new ServerConnection();
        conn.ServerInstance = server;
        conn.LoginSecure = false;
        conn.Login = login;
        conn.Password = password;
        Server srv = new Server(conn);

        foreach (Database db in srv.Databases)
        {
            cbo.Items.Add(db.Name);
        }
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message, "Authentication Error", 
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

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.

public ScriptingOptions GetScriptiongOptions()
{
    ScriptingOptions so = new ScriptingOptions();

    so.ClusteredIndexes = chkClusteredIndexes.Checked;
    so.DriChecks = chkDriChecks.Checked;
    so.DriClustered = chkDriClustered.Checked;
    so.DriDefaults = chkDriDefaults.Checked;
    so.DriForeignKeys = chkDriForeignKeys.Checked;
    so.Indexes = chkIndexes.Checked;
    so.DriIndexes = chkDriIndexes.Checked;
    so.DriNonClustered = chkDriNonClustered.Checked;
    so.DriPrimaryKey = chkDriPrimaryKeys.Checked;
    so.DriUniqueKeys = chkDriUniqueKeys.Checked;
    so.DriWithNoCheck = chkDriWithNoCheck.Checked;
    so.ExtendedProperties = chkExtendedProperties.Checked;
    so.FullTextCatalogs = chkFullTextCatalogs.Checked;
    so.FullTextIndexes = chkFullTextIndexes.Checked;
    so.FullTextStopLists = chkFullTextStopLists.Checked;
    so.NoAssemblies = chkNoAssemblies.Checked;
    so.NoCollation = chkNoCollation.Checked;
    so.NoIdentities = chkNoIdentities.Checked;
    so.NoFileStream = chkNoFileStream.Checked;
    so.NoFileGroup = chkNoFileGroup.Checked;
    so.NoFileStreamColumn = chkNoFileStreamColumn.Checked;
    so.NoIndexPartitioningSchemes = chkNoIndexPartitionSchemes.Checked;
    so.NoTablePartitioningSchemes = chkNoTablePartitionSchemes.Checked;
    so.Permissions = false;
    so.Triggers = chkTriggers.Checked;
    so.NonClusteredIndexes = chkNonClusteredIndexes.Checked;
    so.XmlIndexes = chkXMLIndexes.Checked;
    return so;
}

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.

foreach (ApplicationRole obj in db.ApplicationRoles)
{
    ScriptedObject scriptedObj = new ScriptedObject();
    scriptedObj.Name = obj.Name;
    scriptedObj.Type = "ApplicationRole".ToUpper();
    scriptedObj.Urn = obj.Urn;
    scriptedObj.Schema = obj.DefaultSchema;
    hsObject.Add(scriptedObj.Type.ToUpper() + "." + 
                 scriptedObj.Name.ToLower(), scriptedObj);
}        

...

foreach (XmlSchemaCollection obj in db.XmlSchemaCollections)
{
    ScriptedObject scriptedObj = new ScriptedObject();
    scriptedObj.Name = obj.Name;
    scriptedObj.Type = "XmlSchemaCollection".ToUpper();
    scriptedObj.Schema = obj.Schema;
    scriptedObj.Urn = obj.Urn;
    hsObject.Add(scriptedObj.Type.ToUpper() + "." + 
                 scriptedObj.Name.ToLower(), scriptedObj);
}

All objects are stored in a hsObject hashtable where the key is created by the concatenation of the type and name properties of the object. Next, the array of URNs is retrieved:

var urns = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[hsObject.Count];

int i = 0;
foreach (string key in hsObject.Keys)
{
    urns[i] = new Urn(((ScriptedObject)hsObject[key]).Urn.ToString());
    i++;
}

and the array of URNs is passed as a parameter into the Script() method of the scripter object.

var scripts = scripter.Script(urns);

The Script() method returns the StringCollection object with the generated scripts.

string prevObjName = "";
string prevObjType = "";
foreach (var script in scripts)
{
    string scriptParp = script;
    if (scriptParp.IndexOf("/****** Object:") > -1)
    {
        int objectPos = scriptParp.IndexOf("/****** Object:");
        int scriptDatePos = scriptParp.IndexOf("Script Date:");
        objectPos = objectPos + "/****** Object:".Length;
        string objectName = 
           scriptParp.Substring(objectPos, scriptDatePos - objectPos).Trim();
        string type = "";
        if (objectName.ToUpper() == "FullTextIndex".ToUpper())
        {
            type = prevObjType;
            objectName = prevObjName;
        }
        else
        {
            type = objectName.Substring(0, objectName.IndexOf(" ")).Trim();
            objectName = objectName.Substring(type.Length + 1);
        }

        string schema = "";

        if (objectName.IndexOf("].[") > -1)
        {
            schema = objectName.Substring(0, objectName.IndexOf("].["));
            schema = schema.Replace("[", "").Replace("]", "");
            objectName = objectName.Substring(objectName.IndexOf("].[") + 3);
            objectName = objectName.Replace("[", "").Replace("]", "");

        }
        else if (objectName.IndexOf("[") > -1 && objectName.IndexOf("]") > -1)
        {
            objectName = objectName.Replace("[", "").Replace("]", "");
        }
        else
        {
            objectName = prevObjName;
        }

        if (!lstDBObjecTypes.Contains(type.ToUpper()))
        {
            if (objectName.IndexOf(" ") > -1)
            {
                objectName = objectName.Substring(objectName.IndexOf(" ") + 1);
            }
            type = prevObjType;
            objectName = prevObjName;
        }
        else
        {
            if (scriptParp.IndexOf("/****** Object:") > -1)
            {
                if (scriptParp.IndexOf("******/") > -1)
                {
                    int beginHeaderPos = 
                      scriptParp.IndexOf("/****** Object:") + "/****** Object:".Length;
                    int endHeaderPos = scriptParp.IndexOf("******/");
                    scriptParp = scriptParp.Substring(endHeaderPos + "******/".Length);
                }
            }
            prevObjType = type;
            prevObjName = objectName;
        }
        objectName = objectName.ToLower();

        //alter object, grant ... dorobit

        if (!hsObject.Contains(type.ToUpper() + "." + objectName.ToLower()))
        {
            ScriptedObject scriptedObj = new ScriptedObject();

            scriptedObj.Name = objectName;
            scriptedObj.Type = type.ToUpper();
            scriptedObj.Schema = schema;
            scriptedObj.Urn = "";
            hsObject.Add(scriptedObj.Type + "." + scriptedObj.Name.ToLower(), scriptedObj);
        }

        ((ScriptedObject)hsObject[type.ToUpper() + "." + objectName.ToLower()]).ObjectDefinition = 
         ((ScriptedObject)hsObject[type.ToUpper() + "." + objectName.ToLower()]).ObjectDefinition + 
           scriptParp.Trim() + System.Environment.NewLine + "GO" + System.Environment.NewLine;
    }

    else if (scriptParp.Trim().Substring(0, 5).ToUpper() == "GRANT"
        || scriptParp.Trim().Substring(0, 6).ToUpper() == "REVOKE"
        || scriptParp.Trim().Substring(0, 5).ToUpper() == "DENY")
    {
        string permission = scriptParp; /// permissions COMMING SOON

    }
    else if (scriptParp.Trim().Substring(0, 5).ToUpper() == "ALTER")
    {
        string t = scriptParp.Substring("ALTER".Length).Trim();
        t = t.Substring(0, t.IndexOf(" ")).Trim();
        string n = scriptParp.Substring(scriptParp.IndexOf(t) + t.Length).Trim();
        n = n.Substring(0, n.IndexOf(" ")).Trim();
        if (n.IndexOf("].[") > -1)
        {
            n = n.Substring(n.IndexOf("].[") + 3);
        }
        n=n.Replace("]", "").Replace("[","");
        string key = t.ToUpper() + "." + n.ToLower();

        ((ScriptedObject)hsObject[key]).ObjectDefinition = 
         ((ScriptedObject)hsObject[key]).ObjectDefinition + scriptParp.Trim() + 
           System.Environment.NewLine + "GO" + System.Environment.NewLine;
    }
    else
    {
        ((ScriptedObject)hsObject[prevObjType.ToUpper() + "." + 
          prevObjName.ToLower()]).ObjectDefinition = ((ScriptedObject)hsObject[prevObjType.ToUpper() + 
          "." + prevObjName.ToLower()]).ObjectDefinition + scriptParp.Trim() + 
          System.Environment.NewLine + "GO" + System.Environment.NewLine;
    }
}

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.

History

• 1 June 2011 - Original version posted.