Assert validates that both provided tables (actual and expected) have same columns, same number of rows, and same values in all the rows. Assert will fail if tables signatures are different, row counts are different or when at list one value is different across all table rows.

Assert procedure will compare tables across databases, including objects in the TEMPDB

Arguments
  • v_ActualObjDatabase - Database Name for the Actual object, VARCHAR(128)
  • v_ActualObjSchema - Schema  for the Actual object, VARCHAR(128)
  • v_ActualObjName - Name of the Actual table or view, VARCHAR(128)
  • v_ExpectedObjDatabase - Database Name for the Expected object, VARCHAR(128)
  • v_ExpectedObjSchema - Schema for the Expected object, VARCHAR(128)
  • v_ExpectedObjName - Name of the Expected table or view, VARCHAR(128)
  • v_UserMessage – message to report when assertion fails, NVARCHAR(MAX)

Note: Netezza and Oracle will have this functionality in the future versions, for now it is only available in SQL Server

Ex​amples
SQL Server

CREATE TABLE #ProductTableActual (
  ProductID INT NOT NULL,
  Name varchar (10)
);

INSERT INTO #ProductTableActual (ProductID, Name)
SELECT 7, 'Apple' UNION ALL
SELECT 8, 'Orange' UNION ALL
SELECT 9, 'Banana';

CREATE TABLE #ProductTableExpected (
  ProductID INT NOT NULL,
  Name varchar (10)
);

INSERT INTO #ProductTableExpected (ProductID, Name)
SELECT 1, 'Apple' UNION ALL
SELECT 2, 'Orange' UNION ALL
SELECT 3, 'Banana';

EXEC DBTD_ASSERT_TABLES_ARE_EQUAL
  'tempdb', 'dbo', '#ProductTableActual',
  'tempdb', 'dbo', '#ProductTableExpected',
  'Actual and Expected tables should have same values';

EXEC DBTD_ASSERT_TABLES_ARE_EQUAL
  'MyDatabase', 'MyShema', 'ProductsTable',
  '', '', 'ProductTableExpected',
  'Actual and Expected tables should have same values';

See Also