Copy a table using SQL Server Management Objects

• Download source code

Create an identical copy of a table in a SQL Server Database with SMO? My first thought was no problem at all. But after some minutes of thinking (and after I came to realize that there is no Copy method provided by the Table class), I recognized it will be much harder. The download link at the end of the article contains the sample code.

Introduction

To modify, create or delete a database object, every class (which represents a database entity like Table, Index and so on) contains an Alter, Drop and Create method. As I mentioned in my first article about SMO, the library has a hierarchical structure like the objects in SQL Server. Every object contains a property Parent, so a Database object will return a Server object when the property Parent is called. SMO also allows you to generate the SQL Script, simply call the Script method (which is also implemented by every database entity), it will return a string collection which contains the scripts. You will find a lot more information on MSDN.

Copy the Database Table

Back to our goal, copy a table with the whole structure. First a method copies the table and all the columns.

private Table createTable(Table sourcetable)
{
    Database db = sourcetable.Parent;
    string schema = sourcetable.Schema; 
    Table copiedtable = new Table(db, sourcetable.Name + "_Copy", schema);
    Server server = sourcetable.Parent.Parent;

    createColumns(sourcetable, copiedtable);

    copiedtable.AnsiNullsStatus = sourcetable.AnsiNullsStatus;
    copiedtable.QuotedIdentifierStatus = sourcetable.QuotedIdentifierStatus;
    copiedtable.TextFileGroup = sourcetable.TextFileGroup;
    copiedtable.FileGroup = sourcetable.FileGroup;
    copiedtable.Create();

    return copiedtable;
}

private void createColumns(Table sourcetable, Table copiedtable)
{
    Server server = sourcetable.Parent.Parent;

    foreach (Column source in sourcetable.Columns)
    {
        Column column = new Column(copiedtable, source.Name, source.DataType);
        column.Collation = source.Collation;
        column.Nullable = source.Nullable;
        column.Computed = source.Computed;
        column.ComputedText = source.ComputedText;
        column.Default = source.Default;

        if (source.DefaultConstraint != null)
        {
            string tabname = copiedtable.Name;
            string constrname = source.DefaultConstraint.Name;
            column.AddDefaultConstraint(tabname + "_" + constrname);
            column.DefaultConstraint.Text = source.DefaultConstraint.Text;
        }

        column.IsPersisted = source.IsPersisted;
        column.DefaultSchema = source.DefaultSchema;
        column.RowGuidCol = source.RowGuidCol;

        if (server.VersionMajor >= 10)
        {
            column.IsFileStream = source.IsFileStream;
            column.IsSparse = source.IsSparse;
            column.IsColumnSet = source.IsColumnSet;
        }

        copiedtable.Columns.Add(column);
    }
}

After all the information and columns are set, the table can be created by calling Create. Since SMO supports different SQL Server versions, you will also have to handle this in your code! Unfortunately, there is no enum with the versions, so you have to check the VersionMajor property of the Server object.

Copy all Attached Objects

To have a similar structure in the new table, you will have also to copy the checks, indexes and foreign keys. If you need the identical functionality, you will also have to copy the triggers.

private void createChecks(Table sourcetable, Table copiedtable)
{
    foreach (Check chkConstr in sourcetable.Checks)
    {
        string name = copiedtable.Name + "_"+ chkConstr.Name; 
        Check check = new Check(copiedtable, name);
        check.IsChecked = chkConstr.IsChecked;
        check.IsEnabled = chkConstr.IsEnabled;
        check.Text = chkConstr.Text;
        check.Create();
    }
}

private void createForeignKeys(Table sourcetable, Table copiedtable)
{
    foreach (ForeignKey sourcefk in sourcetable.ForeignKeys)
    {
        string name = copiedtable.Name + "_" + sourcefk.Name;
        ForeignKey foreignkey = new ForeignKey(copiedtable, name);
        foreignkey.DeleteAction = sourcefk.DeleteAction;
        foreignkey.IsChecked = sourcefk.IsChecked;
        foreignkey.IsEnabled = sourcefk.IsEnabled;
        foreignkey.ReferencedTable = sourcefk.ReferencedTable;
        foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema;
        foreignkey.UpdateAction = sourcefk.UpdateAction;

        foreach (ForeignKeyColumn scol in sourcefk.Columns)
        {
            string refcol = scol.ReferencedColumn;            
            ForeignKeyColumn column = 
             new ForeignKeyColumn(foreignkey, scol.Name, refcol);
            foreignkey.Columns.Add(column);
        }

        foreignkey.Create();
    }
}

private void createIndexes(Table sourcetable, Table copiedtable)
{
    foreach (Index srcind in sourcetable.Indexes)
    {
        if (!srcind.IsDisabled && (srcind.IsClustered || 
            (!srcind.IsClustered && !srcind.IsXmlIndex)))
        {
            string name =  copiedtable.Name + "_" + srcind.Name;            
            Index index = new Index(copiedtable, name);

            index.IndexKeyType = srcind.IndexKeyType;
            index.IsClustered = srcind.IsClustered;
            index.IsUnique = srcind.IsUnique;
            index.CompactLargeObjects = srcind.CompactLargeObjects;
            index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys;
            index.IsFullTextKey = srcind.IsFullTextKey;
            index.PadIndex = srcind.PadIndex;
            index.FileGroup = srcind.FileGroup;

            foreach (IndexedColumn srccol in srcind.IndexedColumns)
            {
                IndexedColumn column =
                 new IndexedColumn(index, srccol.Name, srccol.Descending);
                column.IsIncluded = srccol.IsIncluded;
                index.IndexedColumns.Add(column);
            }

            index.Create();
        }
    }
}

The creation of the indexes, checks and foreign keys is similar to the creation of the table itself. So now you only have to call the methods in the correct order to get the full copy.

Transactions and SMO

You can easily execute a set of operations in the same transaction scope, so if something fails, everything will be rolled back. If we take the sample from above and want to make sure that the table will only be copied if everything can be copied, we simply add the TransactionScope object which does everything for us.

public Table Copy(Table sourcetable)
{
    using(TransactionScope scope = new TransactionScope())
    {
       var copiedtable = createTable(sourcetable);

       createChecks(sourcetable, copiedtable);
       createForeignKeys(sourcetable, copiedtable);
       createIndexes(sourcetable, copiedtable);

       scope.complete();
    }

    return copiedtable;
}