Create an identical copy of a table in a SQL Server Database with SMO? My first thought was no problem at all. But after some minutes of thinking (and after I came to realize that there is no Copy
method provided by the Table
class), I recognized it will be much harder. The download link at the end of the article contains the sample code.
Introduction
To modify, create or delete a database object, every class (which represents a database entity like Table, Index and so on) contains an Alter
, Drop
and Create
method. As I mentioned in my first article about SMO, the library has a hierarchical structure like the objects in SQL Server. 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 is also 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 copies 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 the 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 a 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 to the creation of the table itself. So now you only have to call the methods in the correct order to get the full copy.
Transactions and SMO
You can easily execute a set of operations in the same transaction scope, so if something fails, everything will be rolled back. If we take the sample from above and want to make sure that the table will only be copied if everything can be copied, we simply add the TransactionScope
object which does everything for us.
public Table Copy(Table sourcetable)
{
using(TransactionScope scope = new TransactionScope())
{
var copiedtable = createTable(sourcetable);
createChecks(sourcetable, copiedtable);
createForeignKeys(sourcetable, copiedtable);
createIndexes(sourcetable, copiedtable);
scope.complete();
}
return copiedtable;
}