tSQLt: Cross-database testing

A note on cross-database testing
In my previous post, I outlined how to install tSQLt on a specific test database. To test a database called Accounts, I installed tSQLt on the Accounts_Tests database.
It’s worth noting (and I forgot to mention it in the last post) that tSQLt is designed to be installed on the same database that is being tested. However, I want to set up an environment where the framework & tests are kept separate from the Accounts database, for clarity & to make it easier to remove the framework afterwards (it’s easier to drop one database than every tSQLt object).

Since tSQLt is designed to test objects on the same database, installing it on another database will limit functionality. For example, tSQLt.FakeTable (which creates a copy of an existing table without the constraints) doesn’t work when the table is in another database. So instead I have used the following to mimic the basic functionality:

-- Create an empty test table with the same structure
SELECT * INTO [Accounts].[dbo].[Customer_test]
FROM [Accounts].[dbo].[Customer]
WHERE 1 = 0;

-- Rename tables (in the Accounts database)
EXEC [Accounts]..sp_rename 'dbo.Customer', 'Customer_original';
EXEC [Accounts]..sp_rename 'dbo.Customer_test', 'Customer';

-- Insert test data
-- May have to SET IDENTITY_INSERT ON for the insert
INSERT INTO [Accounts].[dbo].[Customer]

Since all changes are made within a transaction that is rolled back at the end of testing, the table renames will be undone.


One comment

  1. […] to run tests against an Accounts database, create a database called Accounts_Tests (Updated: See my note on cross-database testing). Download tSQLt.zip from http://tsqlt.org/downloads/ & unpack. Run SetClrEnabled.sql & […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: