tSQLt.AssertResultSetsHaveSameMetaData [@expectedCommand = ] 'expected command' , [@actualCommand = ] 'actual command'
[@expectedCommand = ] ‘expected command’
A command which returns a result set with the expected meta data to compare with. @expectedCommand is NVARCHAR(MAX) with no default.
[@actualCommand = ] ‘actual command’
The actual result set whose meta data should be compared with the meta data of the result set from the expected command. @actualCommand is NVARCHAR(MAX) with no default.
Return Code Values
Raises a ‘failure’ error if the meta data of the expected command and the meta data of the actual command are not equal.
AssertResultSetsHaveSameMetaData executes the expected command and actual command, capturing the result sets from each. The meta data (i.e. the column names and properties) are compared between the two result sets. If they meta data contains differences, then AssertResultSetsHaveSameMetaData fails the test.
This may be useful, for example, when testing a stored procedure which returns a result set and the names and data types of the columns should be validated.
Example: AssertResultSetsHaveSameMetaData to check the meta data properties of a view
This test case uses AssertResultSetsHaveSameMetaData to check that the meta data of the EmployeeAgeReport view. The view’s meta data is compared against a query provided in the @expectedCommand parameter.
CREATE PROC TestHumanResources.[test EmployeeAgeReport has appropriate meta data] AS BEGIN EXEC tSQLt.AssertResultSetsHaveSameMetaData 'SELECT CAST(''A'' AS VARCHAR(1000)) AS name, CAST(30 AS SMALLINT) AS age', 'SELECT name, age FROM HumanResources.EmployeeAgeReport'; END;
Thanks for the question Jules, I'll send you an email and we can try to figure it out there. Unfortunately, the XML details didn't come through on the post.
Hi, In some causes I encountered an issue when using this Assert against certain views - I am wondering if anyone can shed any light? Basically I was doing the following assert: EXEC tSQLt.AssertResultSetsHaveSameMetaData 'SELECT [Type] FROM #TmpAllAlertsTbl_Expected', 'SELECT [Type] FROM clear.AllAlerts'; Yet I received a failure that says the actual query has returned 3 columns (which surely isn't possible as I have explicitly only asked for [Type], also the view I'm testing actually doesn't contain ID or typeID columns Here's the Error XML: [MetadataTests_VIEWS].[test_clear.AllAlerts.metadata] failed: Expected: but was: I have been able to get around this by creating a second temp table and doing an insert into this from the view - but I shouldn't have to do this. Anyone have any ideas? Cheers, Jules