Monday, 28 November 2011

Library for scripting SQL Server database objects with examples

Introduction

Couple months before I have written article about comparing databases. In my previous article I have described approach using SMO. Using SMO for script generation is very easy but there are some performance issues. I my case it took nearly 4 minutes to compare 2 databases with approximately 2500 objects respectively. After publication of this article I made decision to create my own scripting library and after 3 months I have almost complete version of this library. In this article I will describe library's principle and I show you how to use it on two examples. First example is about comparison of database schemas and second is documentation generating tool.

Background

This library uses dynamic management views for getting information about database objects. This approach is really fast. By this information the collections of objects are populated. When you want to use this library, the first step is to create an ObjectDb object which accepts connection string as parameter. Next you must create an ScriptingOptions object which specify what kind of objects do you want to script and the final step is to call FetchObjects method which takes ScriptingOptions parameter. When fetching of objects is completed, you can access scripted objects by accessing collections of ObjectDb object. Following list represents supported database objects:
  • Tables 
  • Indexes
  • Ddl triggers
  • Dml triggers
  • Clr triggers
  • Stored procedures
  • View
  • Application roles
  • Database roles
  • Users
  • Assemblies
  • Aggregates
  • Defaults
  • Synonyms
  • Xml schema collections
  • Message types
  • Contracts
  • Partition functions
  • Service queues
  • Full text catalogs
  • Full text stop lists
  • Full text indexes
  • Services
  • Broker priorities
  • Partition schemes
  • Remote service bindings
  • Rules
  • Routes
  • Schemas
  • Sql user defined functions
  • Clr user defined functions
  • User defined data types 
  • User defined types
  • User defined table types
Project ObjectHelper contains main classes which are located directly in project folder, database object classes which are located in DBObjectType folder and sql statements important for script generation located in SQL folder.
Note: This library can be used only for databases with compatibility level 90 (MS Sql Server 2005) or 100 (MS Sql Server 2008).  

Using the code

As I mentioned in previous chapter, ObjectHelper project contains several main classes. First of them is BaseDbObject class which is base class for all database object classes. 
public class BaseDbObject
    {
        public string Name{get;set;}
        public long ObjectId{get;set;}
        public string Description { get; set; }
        public DateTime CreateDate { get; set; }
        public DateTime ModifyDate { get; set; }
    }
Next main class is FetchEventArgs class which serves as event class when object is scripted. 
public class FetchEventArgs : EventArgs
    {
        public BaseDbObject DbObject;

        public FetchEventArgs(BaseDbObject obj)
        {
            DbObject = obj;
        }
    }
Most important is ObjectDB class. This class fetches all information about database objects and creates scripts according to ScriptingOptions class. This class has one constructor which accepts connection string parameter. This connection string is used for connection to database. It also has one event called ObjectFetched which fires when object is fetched (when data are collected and database object class is populated using this data). 
public delegate void ObjectFetchedEventHandler(object sender, FetchEventArgs e);

    public class ObjectDb
    {
        readonly string _connString;
        private SqlDatabase _sqlDatabase;
        Hashtable _hsResultSets = new Hashtable();

        public ObjectDb(string connString)
        {
            _connString = connString;
            _tables = new List();
        }

        public event ObjectFetchedEventHandler ObjectFetched;

        protected virtual void OnObjectFetched(FetchEventArgs e)
        {
            if (ObjectFetched != null)
            {
                ObjectFetched(this,e);
            }
        }

        ...

    }
This class contains generic Lists of database objects. 
public List<fulltextindex> FullTextIndexes {get { return _fullTextIndexes;}}
public List<dependency> Dependencies {get{return _dependencies;}}
public List<assembly> Assemblies {get{return _assemblies;}}

...

public List<userdefinedtype> UserDefinedTypes {get{return _userDefinedTypes;}}
public List<userdefinedtabletype> UserDefinedTableTypes {get{return _userDefinedTableTypes;}}
</userdefinedtabletype></userdefinedtype></assembly></dependency></fulltextindex>
FetchObjects method of this class serves for fetching objects. It takes ScriptingOptions parameter. According to ScriptingOptions sql statement for retrieving of information about database objects is generated using ScriptGenerator class. Here is an example demonstrating how scripts for table object are prepared: 
if (so.Tables)
{
    sql.Append("SELECT COUNT(*) FROM sys.tables;");
    sql.AppendLine();
    ResultSets.Add("TableCount", resultSetCount++);
    sql.Append(GetResourceScript("ObjectHelper.SQL.Tables_" + so.ServerMajorVersion + ".sql"));
    sql.AppendLine();
    ResultSets.Add("TableCollection", resultSetCount++);

    if (so.DataCompression)
    {
        if (so.ServerMajorVersion >= 10)
        {
            sql.Append(GetResourceScript("ObjectHelper.SQL.TableDataCompression_" + so.ServerMajorVersion + ".sql"));
            sql.AppendLine();
            ResultSets.Add("TableDataCompressionCollection", resultSetCount++);
        }
        else
        {
            so.DataCompression = false;
        }
    }
    if (so.DefaultConstraints)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.DefaultConstraints.sql"));
        sql.AppendLine();
        ResultSets.Add("DefaultConstraintCollection", resultSetCount++);
    }
    if (so.CheckConstraints)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.CheckConstraints.sql"));
        sql.AppendLine();
        ResultSets.Add("CheckConstraintCollection", resultSetCount++);
    }
    if (so.ForeignKeys)
    {
        sql.Append(GetResourceScript("ObjectHelper.SQL.ForeignKeys.sql"));
        sql.AppendLine();
        ResultSets.Add("ForeignKeyCollection", resultSetCount++);
        ResultSets.Add("ForeignKeyColumnCollection", resultSetCount++);
    }
    sql.Append(GetResourceScript("ObjectHelper.SQL.Columns_" + so.ServerMajorVersion + ".sql"));
    sql.AppendLine();
    ResultSets.Add("ColumnCollection", resultSetCount++);
}
In this case ScriptingOptions are represented by object called so . If Tables property of so object is true then sql statements for tables are appended to sql StringBuilder variable. Scripts for tables are stored in Tables_X.sql files which are stored in SQL folder as embedded resource. The X in file name represents version of SQL Server. Now two versions are supported (90 for SQL Server 2005 and 100 for SQL Server 2010). You can see that sql statement is dynamic generated because only scripts for objects specified by ScriptingOptions are executed. After that, this dynamic sql statement is executed and data are retrieved and objects are fetched. Every time object is fetched, ObjectFetched event is fired. 
if (so.Tables)
{
    DataTable dtTables = ds.Tables[int.Parse(_hsResultSets["TableCollection"].ToString())];
    foreach (DataRow drTable in dtTables.Rows)
    {
        var table = new Table();
        table.AnsiNullsStatus = bool.Parse(drTable["AnsiNullsStatus"].ToString());
        table.ChangeTrackingEnabled = bool.Parse(drTable["ChangeTrackingEnabled"].ToString());
        table.Description = drTable["Description"].ToString();
        …
        OnObjectFetched( new FetchEventArgs(table));
    }
}

Example

Now let’s take a look at the basic example. In following example I will show you how to use this library. The first step is to create ObjectDb object and pass connection string as parameter. Next you must create ScriptingOptions object and specify what kind of objects do you want to script. You can also specify other options like whether to script collation, identity ets. Keep in mind, that you must set ServerMajorVersion. This is important because according to this version scripts for objects are generated. You can set event handler for ObjectFetched event to monitor currently fetched object. The last step is to call FetchObject method which takes ScriptingOptions parameter. If you want to get scripts for tables, just loop through Tables property of ObjectDb object and call Script method of object. 
var objDb = new ObjectDb("server='ANANAS\\ANANAS2009';Trusted_Connection=true;multipleactiveresultsets=false; Initial Catalog='AdventureWorks2008R2'");
var so = new ScriptingOptions { Tables = true, ServerMajorVersion = 10 };
objDb.ObjectFetched += ObjectFetched;
objDb.FetchObjects(so);
foreach (var table in objDb.Tables)
{
    Console.WriteLine("--------------------[" + table.Name + "]--------------------");
    Console.WriteLine(table.Script(so));
}

static void ObjectFetched(object sender, FetchEventArgs e)
{
    Console.WriteLine("Fetched: " + e.DbObject.Name);
}

Database comparison tool

main.jpg Next example which demonstrates how to use this library is database comparison tool. Sometimes when developers work on big systems some inconsistencies in database objects occur. I have faced this problem many times and I decided to create tool which allows me to compare database objects. In my previous article I have created tool based on SMO but this approach was very slow. I have modify this project by using my own scripting library and the performance of this tool rapidly increased. DBCompare project consists of 5 screens: Login, MDIMain, ObjectCompare, ObjectFetch and ScriptView. It also uses external component called DiffereceEngine which is used as a base class for script comparison. More about that class can be find here.

Login screen

login_screen.jpg The login screen is used for creating a connection to the databases. It has two tabs. First tab serves for entering connection information like server, authentication and database name.
scriptingoptions_screen.jpg In second tab you can specify scripting options. Here you can choose what kind of object do you want to script and other options like whether to script identities, collations etc. Here is a list of supported options:
Indexes
Clustered Indexes Gets or sets a Boolean property value that specifies whether statements that define clustered indexes are included in the generated script.
Full Text Indexes Gets or sets the Boolean property value that specifies whether full text indexes are included in the generated script.
Non-Clustered Indexes Gets or sets the Boolean property value that specifies whether non-clustered indexes are included in the generated script.
Misc
Aggregates Gets or sets the Boolean property value that specifies whether aggregates are included in the list of scripted objects.
Script ANSI nulls Gets or sets the Boolean property value that specifies whether to script ANSI nulls.
Script Dependencies Gets or sets the Boolean property value that specifies whether dependencies are included.
Script Quoted identifiers Gets or sets the Boolean property value that specifies whether to script Quoted identifiers
Synonyms Gets or sets the Boolean property value that specifies whether synonyms are included in the list of scripted objects.
XML Schema Collections Gets or sets the Boolean property value that specifies whether xml schema collections are included in the list of scripted objects.
Programmability
Assemblies Gets or sets the Boolean property value that specifies whether assemblies are included in the list of scripted objects.
CLR User Defined Functions Gets or sets the Boolean property value that specifies whether clr user defined functions are included in the list of scripted objects.
Defaults Gets or sets the Boolean property value that specifies whether defaults are included in the list of scripted objects.
Rules Gets or sets the Boolean property value that specifies whether rules are included in the list of scripted objects.
SQL User Defined Functions Gets or sets the Boolean property value that specifies whether sql user defined functions are included in the list of scripted objects.
Stored Procedures Gets or sets the Boolean property value that specifies whether stored procedures are included in the list of scripted objects.
Views Gets or sets the Boolean property value that specifies whether vies are included in the list of scripted objects.
Security
Application Roles Gets or sets the Boolean property value that specifies whether application roles are included in the list of scripted objects.
Schemas Gets or sets the Boolean property value that specifies whether schemas are included in the list of scripted objects.
Users Gets or sets the Boolean property value that specifies whether users are included in the list of scripted objects.
Service Broker
Broker Priority Gets or sets the Boolean property value that specifies whether broker priorities are included in the list of scripted objects.
Message Types Gets or sets the Boolean property value that specifies whether message types are included in the list of scripted objects.
Remote Service Bindings Gets or sets the Boolean property value that specifies whether remote service binding are included in the list of scripted objects.
Routes Gets or sets the Boolean property value that specifies whether routes are included in the list of scripted objects.
Service Queues Gets or sets the Boolean property value that specifies whether service queues are included in the list of scripted objects.
Services Gets or sets the Boolean property value that specifies whether services are included in the list of scripted objects.
Contracts Gets or sets the Boolean property value that specifies whether contracts are included in the list of scripted objects.
Storage
Database roles Gets or sets the Boolean property value that specifies whether database roles are included in the list of scripted objects.
Full Text Catalog Path Gets or sets the Boolean property value that specifies whether full text catalog paths are included in the list of scripted objects.
Full Text Catalogs Gets or sets the Boolean property value that specifies whether full text catalogs are included in the list of scripted objects.
Full Text Stop Lists Gets or sets the Boolean property value that specifies whether full text stop lists are included in the list of scripted objects.
Partition Functions Gets or sets the Boolean property value that specifies whether partition functions are included in the list of scripted objects.
Partition Schemes Gets or sets the Boolean property value that specifies whether partition schemes are included in the list of scripted objects.
Tables
Check Constraints Gets or sets the Boolean property value that specifies whether check constraints are included in the list of table's constraints
Collation Gets or sets the Boolean property value that specifies whether to include the Collation clause in the generated script.
Data Compression Gets or sets the Boolean property value that specifies whether to include the DATA_COMRESSION clause in the generated script.
Default Constraints Gets or sets the Boolean property value that specifies whether default constraints are included in the list of table's constraints
Foreign 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.
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 Identities Gets or sets the Boolean property value that specifies whether definitions of identity property seed and increment are included in the generated script.
Primary Keys Gets or sets the Boolean property value that specifies whether primary key constraints are included in the list of table's constraints
Script Tables Gets or sets the Boolean property value that specifies whether tables are included in the list of scripted objects.
Unique Constraints Gets or sets the Boolean property value that specifies whether unique constraints are included in the list of table's constraints
Triggers
CLR Triggers Gets or sets the Boolean property value that specifies whether clr triggers are included in the list of scripted objects.
DDL Triggers Gets or sets the Boolean property value that specifies whether ddl triggers are included in the list of scripted objects.
DML Triggers Gets or sets the Boolean property value that specifies whether dml triggers are included in the list of scripted objects.
Types
User Defined Data Types Gets or sets the Boolean property value that specifies whether user defined data types are included in the list of scripted objects.
User Defined Table Types Gets or sets the Boolean property value that specifies whether user defined table types are included in the list of scripted objects.
User Defined Types Gets or sets the Boolean property value that specifies whether user defined types are included in the list of scripted objects.

ObjectFetch screen

objectfetch_screen.jpg This screen is the second most important part of application. Here all of the objects are fetched, scripted and then compared. Every object when is fetched is stored in ScriptedObject object: 
public class ScriptedObject
{
    public string Name="";
    public string Schema="";
    public string Type="";
    public DateTime DateLastModified;
    public string ObjectDefinition="";
    public Urn Urn; 
}
When all object are fetched and scripted they are compared and next stored in DataTable object which is passed to the ObjectCompare screen.

ObjectCompare screen

This screen is the main part of project. Here you can compare database objects and see differences between them. This screen has three parts. In first part (left panel) you can select what kind of database objects do you want to compare. In second part (list of database objects) are objects devided into 4 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
object_list.jpg
In the third part you can see the differences between database objects. When you click on one of the items in the list of objects, scripts of the selected objects are compared and displayed.
script_compare.jpg
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.

Database documentation tool

dbdoc_main.jpg Here is an another example how to use my scripting library. This little tool allows you to create html base documentation of your database. For the moment this tool generate documentation for following objects but in the future I will add rest of the objects:
  • Defaults
  • Message types
  • Contracts
  • Service queues
  • Services
  • Broker priorities
  • Indexes
  • Aggregates
  • Assemblies
  • Sql user defined functions
  • Clr user defined functions
  • Stored procedures
  • Views
  • User defined types
  • User defined table types
  • User defined data types
  • Triggers
  • Tables
  • Full text indexes
  • Full text catalogs
  • Full text stop lists
This project called DBDocumentation consists of one screen called Main. This screen has two tabs. In fists tab you can set connection information such as server, credentials and database.
dbdoc_scriptingoptions.jpg In second tab you can choose what kind of objects do you want to document and you can also set what kind of information do you want to have in documentation. Before generation of documentation you must specify the output directory where html documents are going to be saved. This tool can retrieve the list of dependencies for every object and make cross references to them.
Here you can find working example.
documentaion.jpg

Future development

In future I will improve performance of collecting of information used for scripting of database objects. Next I will add the rest of objects to database documentation tool and i will adapt this scripting library for new version of SQL Server.
Posted by at
Labels: , , ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )