“Test-First” Database Development (2)

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 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*/
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'

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.