How do I rename a tSQLt test class?

Question

I'm developing a database using the Red Gate SQL Developer tools. SQL Test, the SSMS add-in that runs tSQLt tests, lacks a way to rename test classes.

I have a test called [BackendLayerCustomerAdministrationTests].[test uspMaintainCustomerPermissions throws error when PermissionValue is missing or empty].

The name is so long it breaks Deployment Manager.

2013-12-05 18:48:40 +00:00 ERROR The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

There are other unwieldly test names in this class, so I want to start by shortening the class name.

A more succinct class name would be CustomerTests.

sp_rename is no help here.

EXECUTE sys.sp_rename
  @objname = N'BackendLayerCustomerAdministrationTests',
  @newname = N'CustomerTests';

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 374 No item by the name of 'BackendLayerCustomerAdministrationTests' could be found in the current database 'ApiServices', given that @itemtype was input as '(null)'.

How do I change it?

Answer 1

tSQLt test classes are schemas with a special extended property.

Cade Roux's great solution for renaming schemas is to create a new schema, transfer all the objects, then drop the old schema.

If we did that here we'd lose the extended property.

Let's adapt it for the tSQLt framework.

How to rename a tSQLt test class

Create a new test class.

EXECUTE tSQLt.NewTestClass
  @ClassName = 'CustomerTests';

You should see the old class and the new class together in the tSQLt.TestClasses view.

SELECT *
FROM tSQLt.TestClasses;

 Name                                      SchemaId
----------------------------------------- ----------
 SQLCop                                           7
 BackendLayerCustomerAdministrationTests         10
 CustomerTests                                   14

Cade used Chris Shaffer's select variable concatenation trick to build a list of transfer statements, and print the result.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql +
N'ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.' + QUOTENAME(name) + N';' +
CHAR(13) + CHAR(10)
FROM sys.objects
WHERE SCHEMA_NAME([schema_id]) = N'BackendLayerCustomerAdministrationTests';

PRINT @sql;

Ugly, but effective.

Copy the output and execute as a new query.

ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.[test uspMaintainCustomer validate merged data];
ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.[test uspMaintainCustomerPermissions throws error when PermissionValue is missing or empty];

I've shown only two tests here, but it should work for all of them.

Now drop the old test class.

EXECUTE tSQLt.DropClass
  @ClassName = N'BackendLayerCustomerAdministrationTests';

The old class should be gone from view.

SELECT *
FROM tSQLt.TestClasses;

 Name                                      SchemaId
----------------------------------------- ----------
 SQLCop                                           7
 CustomerTests                                   14

Run all your tests again to check that it worked.

EXECUTE tSQLt.RunAll;

+----------------------+                                                       
|Test Execution Summary|                                                       
+----------------------+                                                       

|No|Test Case Name                                                              |Result |
+--+----------------------------------------------------------------------------+-------+
|1|[CustomerTests].[test uspMaintainCustomer throws error on missing APIKey]   |Success|
|2|[CustomerTests].[test uspMaintainCustomerPermissions validate merged data]  |Success|
|3|[SQLCop].[test Decimal Size Problem]                                        |Success|
|4|[SQLCop].[test Procedures Named SP_]                                        |Success|
|5|[SQLCop].[test Procedures using dynamic SQL without sp_executesql]          |Success|
|6|[SQLCop].[test Procedures with @@Identity]                                  |Success|
|7|[SQLCop].[test Procedures With SET ROWCOUNT]                                |Success|
-------------------------------------------------------------------------------
Test Case Summary: 7 test case(s) executed, 7 succeeded, 0 failed, 0 errored.
-------------------------------------------------------------------------------

Success!