Click here to Skip to main content
Click here to Skip to main content

Library for scripting SQL Server database objects with examples

, 8 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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<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.

History 

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kanasz Robert
Architect The Staffing Edge & Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist
 
Open source projects: DBScripter - Library for scripting SQL Server database objects
 

Please, do not forget vote

Comments and Discussions

 
QuestionDoes not work Pinmemberdevnet2471-Mar-14 22:23 
AnswerRe: Does not work PinmemberDewey1-Nov-14 11:54 
Questionwow ! PinmemberBigMax31-Jan-14 12:39 
GeneralMy vote of 5 PinmemberTechnoGeek00121-Apr-13 21:46 
Excellent
GeneralRe: My vote of 5 PinmvpKanasz Robert21-Apr-13 22:01 
GeneralMy vote of 5 Pinmemberjotartaglia8216-Jan-13 6:14 
GeneralRe: My vote of 5 PinmvpKanasz Robert4-Mar-13 1:34 
Questionnice!!! Pinmemberstrucker_luc18-Nov-12 4:15 
AnswerRe: nice!!! PinmvpKanasz Robert18-Nov-12 4:21 
QuestionInteresting article and very helpful Pinmemberkr1234564-Nov-12 4:57 
AnswerRe: Interesting article and very helpful PinmvpKanasz Robert4-Nov-12 5:04 
Questiongreat job PinmembersuperdevX151-Nov-12 7:51 
AnswerRe: great job PinmvpKanasz Robert1-Nov-12 7:55 
Questionvery well written article Pinmemberhakon12331-Oct-12 6:32 
AnswerRe: very well written article PinmvpKanasz Robert31-Oct-12 6:37 
Question5 Pinmembermemlon mulas29-Oct-12 6:15 
AnswerRe: 5 PinmvpKanasz Robert29-Oct-12 6:19 
Questiongood and well written article Pinmemberjackhoal27-Oct-12 4:56 
AnswerRe: good and well written article PinmvpKanasz Robert27-Oct-12 5:00 
QuestionExcellent Pinmemberrobkaan27-Oct-12 4:28 
AnswerRe: Excellent PinmvpKanasz Robert27-Oct-12 4:31 
QuestionGood article Pinmemberwindevvv21-Oct-12 7:51 
AnswerRe: Good article PinmvpKanasz Robert21-Oct-12 7:59 
QuestionGood but ... Pinmemberkaslaninovic2-Oct-12 23:50 
AnswerRe: Good but ... PinmvpKanasz Robert3-Oct-12 7:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 8 Nov 2011
Article Copyright 2011 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid