blogs.conchango.com

I recently put up a post with a unit test for check constraints validating all were defined with the NOT FOR REPLICATION option.  I thought it might be helpful to put up a post with tests for all the other object types that can have this option set.

select object_id,name,is_not_for_replication
from sys.foreign_keys where is_not_for_replication <> 1

select object_id,name,is_not_for_replication
from sys.triggers where is_not_for_replication <> 1

select object_id,name,is_not_for_replication 
from sys.check_constraints  where is_not_for_replication <> 1

select object_id,name,is_not_for_replication
from sys.identity_columns where is_not_for_replication <> 1

I guess you could put a UNION ALL in between all these tests and create one test but I would advise against this as it's harder to verify with any degree of accuracy. Strictly speaking I would say that it also wouldn't be a unit test as it is testing multiple object types.

To verify the results of these tests you could again use the Code assertions in Team Edition for database professionals (DataDude) or use SQL assertions.

If you decide to use sql assertions it might be a good idea to come up with a nice pattern for implementing them.

Here's a basic suggestion:

declare  @DbName sysname
           ,@ObjectName sysname
           ,@TestType nvarchar(100)
           ,@TestResult nvarchar(100)
           ,@Message nvarchar(MAX)

select   object_id
           ,name
           ,is_not_for_replication
from  sys.foreign_keys
where  is_not_for_replication <> 1

If @@error <> 0
or @@rowcount <> 0
BEGIN
 select   @DbName  = db_name()
           ,@ObjectName  = N'All Foreign Keys'
           ,@TestType  = N'Schema Test'
           ,@TestResult  = N'FAILED'
           ,@Message  = N'NOT FOR REPLICATION Foreign Keys Test'