How to Clone a Table for Database Unit Testing

Mark Wojciechowicz

Suppose you are writing a unit test for a stored proc which relies on a table that has a number of constraints - foreign keys, non-nullable columns, etc.  It is often useful to mock up the table without all the extra baggage of these constraints so you can load the table with only the data that is actually needed to run the test.  Sure you can do:

     SELECT TOP (0) * FROM myTable

That will get rid of the FKs, but it will not help with non-nullable columns or with keeping identity columns (if you need them).  These things often get in the way of writing unit tests quickly. This post will show how to clone a table without those constraints and how to integrate that into a database unit test project.

I will show parts of the solution in the post, but you can download the whole solution right here.  So let's jump right into the code.  I could come up with some crazy dynamic SQL to get this job done, but it's such a mess to debug and such an eyesore.  So this solution starts relies on SMO.   SMO gives us the relational database world as .NET objects.  In this way, we can easily access each object and its properties to loop through and copy them to a new instance of that object.  For example,

We can create a server object with
      Server myServer = new Server("localhost");
From here, we can get a database with
     Database myDb = myServer.Databases["mydb"];
And now, a particular table as
     Table tb = myDb.Tables["myTable"];

And so it goes, we can get stored procs, views, functions, etc. from a database object and columns and other properties from a table object.  The clone table method looks about like this:
                     Server serv = new Server(connSB.DataSource);
            Database db = serv.Databases[connSB.InitialCatalog];

            string[] sourceTb = sourceTableFQName.Split(new char[]{'.'});
            string srcName = sourceTb[1];
            string srcSchema = sourceTb[0];

            Table sourceTable = db.Tables[srcName, srcSchema];
            Table newTable = new Table(db, newTableName, srcSchema);
            createColumns(sourceTable, newTable, nullable, keepIdentity);
            newTable.AnsiNullsStatus = sourceTable.AnsiNullsStatus;
            newTable.QuotedIdentifierStatus = sourceTable.QuotedIdentifierStatus;
            newTable.TextFileGroup = sourceTable.TextFileGroup;
            newTable.FileGroup = sourceTable.FileGroup;

            //Drop table if exists
            Table test = db.Tables[newTableName, srcSchema];
            if (test != null)

            return newTable;

The createColumns method loops through each column in the table and copies each attribute, optionally bypassing nullability and identity attributes.  I created these methods as part of a TableCloner class, but they could just as well be a TestHelper class if you wanted to make it more generic.
In my solution, I have a SQL database project, database unit test project and the TableCloner class:

Note that the TableCloner class references a few libraries which are necessary for getting SMO to work.
Next, we create a test in the database test project and add references to the TableCloner Class and SMO libraries.  For SQL devs, database unit tests are accessed through the designer in which we plop our SQL code and we are off and running.  However, in this case we are going to right click on the database design form and select view code.  The code page will contain something like this, to which we add the highlighted section:

        public void Clone_a_Table()
            SqlDatabaseTestActions testActions = this.Clone_a_TableData;
            // Execute the pre-test script
            System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
            //Clone table using the execution connection string
            TableCloner.TableCloner cloner = new TableCloner.TableCloner();
            string conn = ExecutionContext.Connection.ConnectionString;
            cloner.cloneTable("dbo.myTable_temp", "myNewTable", conn, true, false);

            SqlExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
                // Execute the test script
                System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
                SqlExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction);


The section above instantiates an instance of the TableCloner class, gets the connection string from the app config file and then executes the cloneTable method.  Note that my source table is in the convention of schema.table and that it has been renamed before I clone it.  In this way, my test can jump right into using my cloned version of the table instead of the original and we can drop the clone and rename the original in our clean up steps.

The database test is configured as such.  In the common scripts we rename the table: 
     exec sp_rename 'dbo.myTable','myTable_temp';
In the pretest, I insert a row, this happens after the cloned table is created: 
    insert into dbo.mynewtable values(1,'test');           

In the test, we check if the row exists.  And finally we drop the clone and rename the original table back.  

    Drop table dbo.myTable;

    exec sp_rename 'dbo.myTable_temp','myTable';

The full solution can be downloaded here.

Labels: ,