Library for scripting SQL Server database objects with examples

Kanasz Robert

4.93/5 (138 votes)

Oct 9, 2011

CPOL

13 min read

250479

13364

This article is about library for scripting SQL Server database objects and examples representing how this library can be used.

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 FullTextIndexes {get { return _fullTextIndexes;}}
public List Dependencies {get{return _dependencies;}}
public List Assemblies {get{return _assemblies;}}

...

public List UserDefinedTypes {get{return _userDefinedTypes;}}
public List UserDefinedTableTypes {get{return _userDefinedTableTypes;}}

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

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

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.

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

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

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.

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

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.

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.

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.

History

9 October 2011 - Original version posted.
1 November 2011 - Article modified