Procedure will generate the Unit Test SQL Script for specified database object. For tables, procedures and functions code will generate asserts that verify table columns, verifies parameters and checks the number of therm to make sure that a Unit Test can catch when bew parameters ar colunds were added .

Arguments

  • Required parameters
    • v_FullObjectName – full object name in the MyServer.MySchema.MyObject format. SYSNAME
    • v_UnitTestSQL – this is OUTPUT parameter. The SQL Script of the Unit Test; NVARCHAR(MAX) 
    • v_UnitTestName – this is OUTPUT parameter. The name of the Unit Tests; NVARCHAR(128)
  • Optional parameters
    • v_UnitTestSuite – The name of the Unit Test Suite. Default value 'DBSchemaTests'. SYSNAME  
    • v_FrameworkDatabase – the database where DBTestDriven framework has been installed. We recommend to install the framework in to its own database. Default value DBTestDriven. SYSNAME
    • v_GenerateAssertsThatCheckTableAndViewColumns – set the value to 1 to generate assertion for tables or views columns. Default value 1. BIT 
    • v_GenerateAssertsThatCheckTableIndexes – set the value to 1 to generate assertion of table indexes. Default value is 1. BIT  
    • v_AutoGenMessage – a message that will be added as a comment within the body of generated Unit Test SQL Script. Default value is NULL. VARCHAR(500)
    • v_GenerateAssertsThatCheckParameters - set the value to 1 to generate parameter asserts for an object that accepts parameters. Default value is 1. BIT

NOTE:
Supported in SQL Server versions at this time.

Examples

SQL Server

Run the script below to generate unit test for the [AdventureWorks].[Sales].[Store] table 

DECLARE @v_UnitTestSQL NVARCHAR(MAX),
        @v_UnitTestName NVARCHAR(128)
 
EXEC DBO.DBTD_CREATE_SCHEMA_UNIT_TEST
    @v_FullObjectName = '[AdventureWorks].[Sales].[Store]',
    @v_UnitTestSQL = @v_UnitTestSQL OUTPUT,
    @v_UnitTestName = @v_UnitTestName OUTPUT,
    @v_UnitTestSuite = 'AdventureWorksSchemaTests',
    @v_FrameworkDatabase = 'DBTestDriven'
 
--Check the resulted SQL Script
SELECT @v_UnitTestSQL AS [@v_UnitTestSQL],
       @v_UnitTestName AS [@v_UnitTestName]

 

The output of this script will produce following results:

 @v_UnitTestName = [UT_AdventureWorksSchemaTests_SALES_Store]

and the Unit Test SQL Script that can be saved in the file
 
/***********************************************************************************/ 
USE DBTestDriven 
GO 
 
/**********************************************************************************/ 
EXEC DBTD_DROP_PROC_IF_EXISTS '[UT_AdventureWorksSchemaTests_SALES_Store]', ''; 
GO 
 
/*CC20119F-86F8-4208-BE44-70BB99A06EC7*/ 
CREATE PROCEDURE [UT_AdventureWorksSchemaTests_SALES_Store] 
(@v_Debug BIT = 0)  
AS  
BEGIN  
      EXEC DBTD_UNIT_TEST 'AdventureWorksSchemaTests'; 
      EXEC DBTD_USE_TRANSACTION 'Just in case' 
      EXEC DBTD_ASSERT_TABLE_EXISTS '[AdventureWorks].[Sales].[Store]',
         'Cannot find USER_TABLE object in the [AdventureWorks] database'; 
 
      /*Check Related Objects*/ 
      EXEC DBTD_ASSERT_DEFAULT_CONSTRAINT @v_DefaultConstrant='DF_Store_rowguid',
         @v_TableName='[AdventureWorks].[Sales].    [Store]',         
         @v_DefaultConstrantDefinition='(newid())',
         @v_UserMessage='Cannot find DEFAULT_CONSTRAINT in the [AdventureWorks] database';  
      EXEC DBTD_ASSERT_DEFAULT_CONSTRAINT @v_DefaultConstrant='DF_Store_ModifiedDate',      
         @v_TableName='[AdventureWorks].[Sales].[Store]',    
         @v_DefaultConstrantDefinition='(getdate())',     
         @v_UserMessage='Cannot find DEFAULT_CONSTRAINT in the [AdventureWorks] database';  
 
      /*Check TABLE Columns*/ 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'BusinessEntityID', 'Cannot find column [BusinessEntityID] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'BusinessEntityID', 'int', 'Column [BusinessEntityID] does not have expected type of [int] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE '[AdventureWorks].[Sales].[Store]', 'BusinessEntityID', 'Column [BusinessEntityID] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'Name', 'Cannot find column [Name] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'Name', 'Name', 'Column [Name] does not have expected type of [Name] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE '[AdventureWorks].[Sales].[Store]', 'Name', 'Column [Name] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'SalesPersonID', 'Cannot find column [SalesPersonID] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'SalesPersonID', 'int', 'Column [SalesPersonID] does not have expected type of [int] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NULLABLE '[AdventureWorks].[Sales].[Store]', 'SalesPersonID', 'Column [SalesPersonID] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'Demographics', 'Cannot find column [Demographics] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'Demographics', 'xml', 'Column [Demographics] does not have expected type of [xml] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NULLABLE '[AdventureWorks].[Sales].[Store]', 'Demographics', 'Column [Demographics] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'rowguid', 'Cannot find column [rowguid] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'rowguid', 'uniqueidentifier', 'Column [rowguid] does not have expected type of [uniqueidentifier] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE '[AdventureWorks].[Sales].[Store]', 'rowguid', 'Column [rowguid] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_EXISTS '[AdventureWorks].[Sales].[Store]', 'ModifiedDate', 'Cannot find column [ModifiedDate] in the [AdventureWorks].[Sales].[Store]'; 
EXEC DBTD_ASSERT_COLUMN_TYPE '[AdventureWorks].[Sales].[Store]', 'ModifiedDate', 'datetime', 'Column [ModifiedDate] does not have expected type of [datetime] in the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE '[AdventureWorks].[Sales].[Store]', 'ModifiedDate', 'Column [ModifiedDate] NULLABILITY criteria does not match expectations in the [AdventureWorks].[Sales].[Store] table'; 
 
/*Check Table Indexes*/ 
EXEC DBTD_ASSERT_INDEX_EXISTS 'PK_Store_BusinessEntityID', '[AdventureWorks].[Sales].[Store]', 'Cannot find index [PK_Store_BusinessEntityID] for the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_INDEX_CLUSTERED 'PK_Store_BusinessEntityID', '[AdventureWorks].[Sales].[Store]', 'Index [PK_Store_BusinessEntityID] expected to be CLUSTERED for the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_INDEX_EXISTS 'AK_Store_rowguid', '[AdventureWorks].[Sales].[Store]', 'Cannot find index [AK_Store_rowguid] for the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_INDEX_EXISTS 'IX_Store_SalesPersonID', '[AdventureWorks].[Sales].[Store]', 'Cannot find index [IX_Store_SalesPersonID] for the [AdventureWorks].[Sales].[Store] table'; 
EXEC DBTD_ASSERT_INDEX_EXISTS 'PXML_Store_Demographics', '[AdventureWorks].[Sales].[Store]', 'Cannot find index [PXML_Store_Demographics] for the [AdventureWorks].[Sales].[Store] table'; 
 
END 
GO 
 
      

 

See Also