I’ve made additions and changes to the GitHub repository for “Test First” development of the database schema. All procedure names will now start with “Test”, so that they’re easier to find in Object Explorer and Intellisense. I’ve added a “TestHeader” stored procedure. You can put this at the top of your TDD script and it’ll add the date/time, database user, Sql Server version etc., to your results.

The spObjectExists procedure has been replaced by TestObjectExists, with the following signature:
CREATE PROCEDURE [dbo].[TestObjectExists]
@i_ObjectType sysname
,@i_ObjectName sysname = Null
,@i_ParentSchemaName sysname = Null --not needed if testing schema's existence
,@i_DbName sysname = Null
AS

As you can see it now finds objects in other databases on the server. The parameter ordering and defaults allow you to skip the last two parameters if you are only querying the dbo schema in the local database. There are new object types included, like “DATABASE”, “DATABASE_ROLE”, “SYNONYM”. Some examples of usage are as follows:

/*Test Script*/
TestHeader
TestObjectExists 'DATABASE', 'msdb'
TestObjectExists 'SCHEMA', 'dbo'
TestObjectExists 'SCHEMA', 'dbo', Null, 'msdb'
TestObjectExists 'VIEW', 'all_objects', 'sys'
TestObjectExists 'USER_TABLE', 'backupset', 'dbo', 'msdb'
TestObjectExists 'DATABASE_ROLE', '{Your Role Name}', Null, 'Test'
TestObjectExists 'USER_DEFINED_TABLE_TYPE', '{Your User Defined Table Type Name}', Null, 'msdb'