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.