How to copy a database of tables to a schema name in Microsoft.SqlServer.Management.Smo.Transfer

Question

I am copying tables from the Destination_Test database of the Source_Test database.

Using Microsoft.SqlServer.Management.Smo.Transfer .

It copies all tables from Source_Test to Destination_Test

But I want the tables to have the "TestSchema" schema.

The main problem is that it copies the entire table with dbo schema . Even I set this.CopyAllTables = false; but he copies it. How to limit it. I tried the 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 he set to copy tables from only one schema by providing SchemaName, he will add only schema tables for copying.

The main thing is to create a collection of tables that you want to copy, and explicitly add this collection to the ObjectList. Remember to change the names of ServerName and 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 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(); } } }