In a way similar to the DBTD_ASSERT_TABLES_ARE_EQUAL logic, assert validates two tables or row sets (actual and expected) that might have similar signatures. Assertion can performed in one of the two ways: based on the Actual table columns; or based on the Expected table columns. Allowing users to check only required columns. 

When Compare Option set to use an Expected table as the comparison base, assert validates that actual table have all columns that Expected table have, and all those columns have same number of rows, same values in all the rows.

When Compare Option set to use an Actual table as the comparison base, assert validates that Expected table have all columns that Actual table have, and all those columns have same number of rows, same values in all the rows.

Assert will fail if signatures are different, row counts are different, or when at list one value is the different across all table rows.

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

Argume​nts
  • 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_CompareOption -compare option: “USE ACTUAL COLUMNS”, “USE EXPECTED COLUMNS”
  • v_UserMessage – message to report when assertion fails, Variable Character(255)

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

Examples

SQL Server

CREATE TABLE #ProductTableActual (
  ProductID INT NOT NULL,
  Name varchar (10),
  [Description] varchar (10),
  Manufacture varchar (10),
  Quantity INT
);

INSERT INTO #ProductTableActual
    (ProductID, Name, [Description], Manufacture, Quantity)
    VALUES ( 1, 'Apple', 'Green', 'Farm A', 10 ),
           ( 2, 'Orange', 'Orange', 'Farm B', 20 ),
           ( 3, 'Banana', 'Yellow', 'Farm C', 30 );

--to check that product name matching ProductID
CREATE TABLE #ProductTableExpected (
  ProductID INT NOT NULL,
  Name varchar (10)
);

INSERT INTO #ProductTableExpected

    (ProductID, Name)
    VALUES ( 1, 'Apple'),
           ( 2, 'Orange'),
           ( 3, 'Banana');

EXEC DBTD_ASSERT_ROWSETS_ARE_EQUAL
  '', '', '#ProductTableActual',
  '', '', '#ProductTableExpected',
  'USE EXPECTED COLUMNS',

  '(A) Product Name does not matching ProductID';

See Also