Click here to Skip to main content
Click here to Skip to main content
Go to top

Copy a table using SQL Server Management Objects

, 10 Aug 2010
Rate this:
Please Sign up or sign in to vote.
How to copy a table using SQL Server Management Objects

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;
}

License

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

Share

About the Author

Mattia Baldinger
Software Developer
Switzerland Switzerland
No Biography provided
Follow on   Twitter

Comments and Discussions

 
QuestionProblems using TransactionScope PinmemberScherzoLCo26-May-13 22:37 
SuggestionI'm way to lazy to do all that. Looking at code gives me a headache. PinmemberRouaan31-Aug-12 7:47 
GeneralMy vote of 4 PinmemberRay Guan24-Jan-11 19:20 
QuestionSELECT INTO? PinmemberS.H.Bouwhuis17-Aug-10 5:00 
AnswerRe: SELECT INTO? [modified] PinmemberRob Siklos18-Aug-10 14:58 
GeneralRe: SELECT INTO? PinmemberMattia Baldinger18-Aug-10 21:02 

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 | Mobile
Web02 | 2.8.140916.1 | Last Updated 10 Aug 2010
Article Copyright 2010 by Mattia Baldinger
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid