How to copy tables base on schema name in Microsoft.SqlServer.Management.Smo.Transfer

Question

I am copying tables from Source_Test database Destination_Test database.

Using Microsoft.SqlServer.Management.Smo.Transfer.

It's copies all the tables from Source_Test to Destination_Test

But i want tables having schema 'TestSchema'.

Main problem is, it copies all table having dbo schema. Even i set this.CopyAllTables = false; still it copies it. How to restrict it. I had tried following :

public class CopyTable : Transfer, ISchemaCopy
{
   private void CopyTables()
   {
      this.CopyAllTables = false;
      var server = new Server(new ServerConnection(connection));
      var script = this.ScriptTransfer().Cast<string>();
   }
}

Answer 1

I think the code below does what you are trying to achieve. If it's set to copy tables from only one schema by providing a SchemaName it will add only that schema's tables for copying.

The key thing is building up a collection of tables that are the ones you want to copy and explicitly adding that collection to ObjectList. Don't forget to change the ServerName and the DatabaseName to something real.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

/* Needs references to:
 * 
 * System.Data
 * Microsoft.SqlServer.ConnectionInfo 
 * Microsoft.SqlServer.Management.Sdk.Sfc
 * Microsoft.SqlServer.Smo
 * Microsoft.SqlServer.SmoExtended
 */

namespace CopyTablesExample
{
    class Program
    {
        public class CopyTables : Transfer
        {
            public string SchemaName { get; set; }
            public string DatabaseName { get; set; }
            public string ServerName { get; set; }


            public IEnumerable<string> GetScript()
            {
                //This is what we will return
                IEnumerable<string> scriptStrings = null;

                if (this.DatabaseName == null) {
                    throw new Exception("DatabaseName property not set.");
                }

                if (this.ServerName == null) {
                    throw new Exception("ServerName property not set.");
                }

                var server = new Server(new ServerConnection(ServerName));
                this.Database = server.Databases[this.DatabaseName];

                //Turn off all objects.  Below we will start turning on what we want.
                //You may wish to add more object types.
                this.CopyAllObjects = false;

                if (this.SchemaName == null) {
                    //No schema means all tables
                    this.CopyAllTables = true;
                }
                else {
                    //A specific schema means all tables from that schema
                    this.CopyAllTables = false;
                    //We only want to copy tables in a specific schema.
                    List<Table> tablesToCopy = new List<Table>();
                    foreach (Table t in this.Database.Tables) {
                        if (t.Schema.Equals(this.SchemaName)) {
                            tablesToCopy.Add(t);
                        }
                    }

                    //Add specifically the tables we want which are from the schema we want
                    this.ObjectList.AddRange(tablesToCopy);
                }

                try {
                    scriptStrings = this.ScriptTransfer().Cast<string>();
                }
                catch (Exception ex) {
                    Console.WriteLine("We got an exception.");
                    throw;
                }
                return scriptStrings;
            }
        }

        static void Main(String[] Args)
        {
            //Only set a SchemaName in line below, when you want to restrict yourself to copying that schema's tables.
            CopyTables ct = new CopyTables() { ServerName = "xyz", DatabaseName = "abc", SchemaName = "junk" } ; 
            var copyTablesScript = ct.GetScript();

            //For validation, display the script as generated
            foreach (var item in copyTablesScript) {
                Console.WriteLine(item);
            }
            var discard = Console.ReadKey();
        }
    }
}