Copy a table using SQL Server Management Objects

0 comments

To modify, create or delete a database object, every class (which represents a database entity like Table, Index and so on) contains a Alter, Drop and Create method. 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 also is 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 has copy 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 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 the 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 than the creation of the table itself. So now you only have to call the methods in the correct order to get the full copy.

READ MORE >>
http://www.dotnetfunda.com/articles/article1012-copy-a-table-using-sql-server-management-objects-.aspx

0 comments: