Create a copy of partitioned tables using SMO

Question

I'm trying to use SMO to replicate a copy of a partitioned table:

using (var scope = new TransactionScope()) 
{ 
    var copiedtable = CreateTable(sourcetable);

    createColumns(sourcetable, copiedtable);
    CreateIndexes(sourcetable, copiedtable);
    CreateForeignKeys(sourcetable, copiedtable);

    sourcetable.PartitionScheme = sourcetable.PartitionScheme + "1";

    scope.Complete();
    return copiedtable; 
 }

The copy is created but the partition scheme is ignored, is there a way to align to the partition scheme? Am I going about things the wrong way? I receive no error message or exception the code silently creates an unpartitioned copy of the partitioned table.

I want to automate as I have several hundred tables where the partitions are aligned to the wrong schema.

score 0 · Answer 1 · answered Nov 08 '12 at 15:40

I found a workaround by scripting the definition out into a StringBuilder and then doing a find and replace.

        var scriptOptions = new ScriptingOptions
                                {
                                    ClusteredIndexes = true,
                                    Default = true,
                                    FullTextIndexes=true,
                                    Indexes=true,
                                    NonClusteredIndexes = true,
                                    SchemaQualify = true,
                                    DriAllConstraints = true
                                };

        var tableScripts = table.Script(scriptOptions);
        String indexName = null;

        // Adjust to new table name
        sb.Replace(tableName, tableNameNew);

        //
        sb.Replace(partitionName, partitionName + "_New")

Create a copy of partitioned tables using SMO

1 Answers1