Click here to Skip to main content
15,860,972 members
Articles / Database Development / SQL Server / SQL Server 2008

Library for scripting SQL Server database objects with examples

Rate me:
Please Sign up or sign in to vote.
4.93/5 (138 votes)
8 Nov 2011CPOL13 min read 229.5K   13.3K   252   111
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.

C#
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.

C#
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).

C#
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.
C#
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:
C#
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.
C#
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.

C#
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 IndexesGets or sets a Boolean property value that specifies whether statements that define clustered indexes are included in the generated script.
Full Text IndexesGets or sets the Boolean property value that specifies whether full text indexes are included in the generated script.
Non-Clustered IndexesGets or sets the Boolean property value that specifies whether non-clustered indexes are included in the generated script.
Misc
AggregatesGets or sets the Boolean property value that specifies whether aggregates are included in the list of scripted objects.
Script ANSI nullsGets or sets the Boolean property value that specifies whether to script ANSI nulls.
Script DependenciesGets or sets the Boolean property value that specifies whether dependencies are included.
Script Quoted identifiersGets or sets the Boolean property value that specifies whether to script Quoted identifiers
SynonymsGets or sets the Boolean property value that specifies whether synonyms are included in the list of scripted objects.
XML Schema CollectionsGets or sets the Boolean property value that specifies whether xml schema collections are included in the list of scripted objects.
Programmability
AssembliesGets or sets the Boolean property value that specifies whether assemblies are included in the list of scripted objects.
CLR User Defined FunctionsGets or sets the Boolean property value that specifies whether clr user defined functions are included in the list of scripted objects.
DefaultsGets or sets the Boolean property value that specifies whether defaults are included in the list of scripted objects.
RulesGets or sets the Boolean property value that specifies whether rules are included in the list of scripted objects.
SQL User Defined FunctionsGets or sets the Boolean property value that specifies whether sql user defined functions are included in the list of scripted objects.
Stored ProceduresGets or sets the Boolean property value that specifies whether stored procedures are included in the list of scripted objects.
ViewsGets or sets the Boolean property value that specifies whether vies are included in the list of scripted objects.
Security
Application RolesGets or sets the Boolean property value that specifies whether application roles are included in the list of scripted objects.
SchemasGets or sets the Boolean property value that specifies whether schemas are included in the list of scripted objects.
UsersGets or sets the Boolean property value that specifies whether users are included in the list of scripted objects.
Service Broker
Broker PriorityGets or sets the Boolean property value that specifies whether broker priorities are included in the list of scripted objects.
Message TypesGets or sets the Boolean property value that specifies whether message types are included in the list of scripted objects.
Remote Service BindingsGets or sets the Boolean property value that specifies whether remote service binding are included in the list of scripted objects.
RoutesGets or sets the Boolean property value that specifies whether routes are included in the list of scripted objects.
Service QueuesGets or sets the Boolean property value that specifies whether service queues are included in the list of scripted objects.
ServicesGets or sets the Boolean property value that specifies whether services are included in the list of scripted objects.
ContractsGets or sets the Boolean property value that specifies whether contracts are included in the list of scripted objects.
Storage
Database rolesGets or sets the Boolean property value that specifies whether database roles are included in the list of scripted objects.
Full Text Catalog PathGets or sets the Boolean property value that specifies whether full text catalog paths are included in the list of scripted objects.
Full Text CatalogsGets or sets the Boolean property value that specifies whether full text catalogs are included in the list of scripted objects.
Full Text Stop ListsGets or sets the Boolean property value that specifies whether full text stop lists are included in the list of scripted objects.
Partition FunctionsGets or sets the Boolean property value that specifies whether partition functions are included in the list of scripted objects.
Partition SchemesGets or sets the Boolean property value that specifies whether partition schemes are included in the list of scripted objects.
Tables
Check ConstraintsGets or sets the Boolean property value that specifies whether check constraints are included in the list of table's constraints
CollationGets or sets the Boolean property value that specifies whether to include the Collation clause in the generated script.
Data CompressionGets or sets the Boolean property value that specifies whether to include the DATA_COMRESSION clause in the generated script.
Default ConstraintsGets or sets the Boolean property value that specifies whether default constraints are included in the list of table's constraints
Foreign KeysGets 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 StreamGets or sets an object that specifies whether to include the FILESTREAM_ON clause when you create VarBinaryMax columns in the generated script.
No IdentitiesGets or sets the Boolean property value that specifies whether definitions of identity property seed and increment are included in the generated script.
Primary KeysGets or sets the Boolean property value that specifies whether primary key constraints are included in the list of table's constraints
Script TablesGets or sets the Boolean property value that specifies whether tables are included in the list of scripted objects.
Unique ConstraintsGets or sets the Boolean property value that specifies whether unique constraints are included in the list of table's constraints
Triggers
CLR TriggersGets or sets the Boolean property value that specifies whether clr triggers are included in the list of scripted objects.
DDL TriggersGets or sets the Boolean property value that specifies whether ddl triggers are included in the list of scripted objects.
DML TriggersGets or sets the Boolean property value that specifies whether dml triggers are included in the list of scripted objects.
Types
User Defined Data TypesGets or sets the Boolean property value that specifies whether user defined data types are included in the list of scripted objects.
User Defined Table TypesGets or sets the Boolean property value that specifies whether user defined table types are included in the list of scripted objects.
User Defined TypesGets 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:

C#
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)


Written By
Architect 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

 
GeneralMy vote of 5 Pin
Member 1209834329-Oct-15 7:16
Member 1209834329-Oct-15 7:16 
QuestionDoes not work Pin
devnet2471-Mar-14 21:23
devnet2471-Mar-14 21:23 
AnswerRe: Does not work Pin
Dewey1-Nov-14 10:54
Dewey1-Nov-14 10:54 
Questionwow ! Pin
BigMax31-Jan-14 11:39
professionalBigMax31-Jan-14 11:39 
GeneralMy vote of 5 Pin
npdev1321-Apr-13 20:46
npdev1321-Apr-13 20:46 
GeneralRe: My vote of 5 Pin
Kanasz Robert21-Apr-13 21:01
professionalKanasz Robert21-Apr-13 21:01 
GeneralMy vote of 5 Pin
jotartaglia8216-Jan-13 5:14
jotartaglia8216-Jan-13 5:14 
GeneralRe: My vote of 5 Pin
Kanasz Robert4-Mar-13 0:34
professionalKanasz Robert4-Mar-13 0:34 
Questionnice!!! Pin
strucker_luc18-Nov-12 3:15
strucker_luc18-Nov-12 3:15 
AnswerRe: nice!!! Pin
Kanasz Robert18-Nov-12 3:21
professionalKanasz Robert18-Nov-12 3:21 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:57
kr1234564-Nov-12 3:57 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:04
professionalKanasz Robert4-Nov-12 4:04 
Questiongreat job Pin
superdevX151-Nov-12 6:51
superdevX151-Nov-12 6:51 
AnswerRe: great job Pin
Kanasz Robert1-Nov-12 6:55
professionalKanasz Robert1-Nov-12 6:55 
Questionvery well written article Pin
hakon12331-Oct-12 5:32
hakon12331-Oct-12 5:32 
AnswerRe: very well written article Pin
Kanasz Robert31-Oct-12 5:37
professionalKanasz Robert31-Oct-12 5:37 
Question5 Pin
memlon mulas29-Oct-12 5:15
memlon mulas29-Oct-12 5:15 
AnswerRe: 5 Pin
Kanasz Robert29-Oct-12 5:19
professionalKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:56
jackhoal27-Oct-12 3:56 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:00
professionalKanasz Robert27-Oct-12 4:00 
QuestionExcellent Pin
robkaan27-Oct-12 3:28
robkaan27-Oct-12 3:28 
AnswerRe: Excellent Pin
Kanasz Robert27-Oct-12 3:31
professionalKanasz Robert27-Oct-12 3:31 
QuestionGood article Pin
windevvv21-Oct-12 6:51
windevvv21-Oct-12 6:51 
AnswerRe: Good article Pin
Kanasz Robert21-Oct-12 6:59
professionalKanasz Robert21-Oct-12 6:59 
QuestionGood but ... Pin
kaslaninovic2-Oct-12 22:50
kaslaninovic2-Oct-12 22:50 

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

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