Copying a table using SMO

This is a sample code to create a copy of a table using Server Management Objects (SMO).

The class DBObject is used to hold the names of the table and its related functions and views that need to be copied. Currently, for simplicity sake, I will only keep the table name. It can be further extended to store other db object names.


using System;
using System.Collections.Specialized;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SMODemo
{
public class DBObject
{
public string TableName { get; set; }

public DBObject(string tableName)
{
this.TableName = tableName;
}

// Can be further extended to save the dependent view names and functions

}

The following code is a console application. It creates a copy of the Employee table in the SO database. The user is prompted for the destination table name. The columns, indexes and keys (primary and foreign) are copied when the table is copied.


class Program
{
static Database db;

static void Main(string[] args)
{
CopyMain();
}

private static void CopyMain()
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["SO"];

//Connect to the local, default instance of SQL Server.
SqlConnection conn = new SqlConnection(connectionString);
Server srv = new Server(new ServerConnection(conn));

//Reference the SO database.
db = srv.Databases["SO"];

Console.WriteLine("Enter destination table name:");

string destinationTableName = Console.ReadLine();
DBObject destinationObject = new DBObject(destinationTableName);
DBObject sourceObject = new DBObject("Employee");

CopyObjects(sourceObject, destinationObject);
}

private static void CopyObjects(DBObject sourceObject, DBObject destinationObject)
{
Table table = db.Tables[sourceObject.TableName];
CopyTable(table, destinationObject.TableName);

// Can be further extended to copy the dependent views and functions
}

private static void CopyTable(Table sourceTable, string destinationName)
{
Table destinationTable = new Table(db, destinationName);

foreach (Column col in sourceTable.Columns)
destinationTable.Columns.Add(CopyColumn(destinationTable, col));

foreach (Index index in sourceTable.Indexes)
destinationTable.Indexes.Add(CopyIndex(destinationTable, index, sourceTable.Name));

foreach (ForeignKey fk in sourceTable.ForeignKeys)
destinationTable.ForeignKeys.Add(CopyForeignKeys(destinationTable, fk, sourceTable.Name));

// TODO: Create copy for Triggers

destinationTable.Create();
}

private static Column CopyColumn(Table parent, Column sourceColumn)
{
Column destinationColumn = new Column(parent, sourceColumn.Name, sourceColumn.DataType);

if (sourceColumn.Computed)
{
destinationColumn.Computed = true;
destinationColumn.ComputedText = sourceColumn.ComputedText;
}

if (sourceColumn.Identity)
{
destinationColumn.Identity = true;
destinationColumn.IdentityIncrement = sourceColumn.IdentityIncrement;
destinationColumn.IdentitySeed = sourceColumn.IdentitySeed;
}

destinationColumn.Default = sourceColumn.Default;
destinationColumn.Nullable = sourceColumn.Nullable;

return destinationColumn;
}

private static Index CopyIndex(Table parent, Index sourceIndex, string sourceTableName)
{
Index destinationIndex = new Index(parent, sourceIndex.Name.Replace(sourceTableName, parent.Name));
destinationIndex.IndexKeyType = sourceIndex.IndexKeyType;

foreach (IndexedColumn sourceIndexedCol in sourceIndex.IndexedColumns)
{
destinationIndex.IndexedColumns.Add(new IndexedColumn(destinationIndex, sourceIndexedCol.Name, sourceIndexedCol.Descending));
}

return destinationIndex;
}

private static ForeignKey CopyForeignKeys(Table parent, ForeignKey sourceForeignKey, string sourceTableName)
{
ForeignKey destinationForeignKey = new ForeignKey(parent, sourceForeignKey.Name.Replace(sourceTableName, parent.Name));

foreach (ForeignKeyColumn fkSourceCol in sourceForeignKey.Columns)
destinationForeignKey.Columns.Add(new ForeignKeyColumn(destinationForeignKey, fkSourceCol.Name, fkSourceCol.ReferencedColumn));

destinationForeignKey.DeleteAction = sourceForeignKey.DeleteAction;
destinationForeignKey.IsChecked = sourceForeignKey.IsChecked;
destinationForeignKey.IsEnabled = sourceForeignKey.IsEnabled;
destinationForeignKey.ReferencedTable = sourceForeignKey.ReferencedTable;
destinationForeignKey.ReferencedTableSchema = sourceForeignKey.ReferencedTableSchema;
destinationForeignKey.UpdateAction = sourceForeignKey.UpdateAction;

return destinationForeignKey;
}
}
}


About these ads