TSQLT Asserting stored procedure column names

Question

Is there a way for tsqlt to assert all the columns returned from a stored procedure have a certain column name? I need to check if Employee_lname column actually returns as 'Employee_lname' instead of (no column name)

Answer 1

You could try using one of these DMV's. They are designed to output in table form a description of the first result set returned by a query/object.

USE TargetDatabase
GO

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(@Object_ID_Of_Proc, 0) o

SELECT *
FROM sys.dm_exec_describe_first_result_set('EXEC usp_NameOfProcYouAreTesting', NULL, 0)

Documentation:

• dm_exec_describe_first_result_set_for_object
• dm_exec_describe_first_result_set

---

It should be noted that if you have dynamic output, for example by using IF/THEN/ELSE or by using dynamic SQL, this won't work for you. These DMV's do not actually run the query and parse the output.

Example of dynamic output:

CREATE PROCEDURE #usp_dynamic_output
AS
IF (RAND() > 0.5)
    SELECT Col1 = 1, Col2 = 2
ELSE
    SELECT Col3 = 3
GO
SELECT *
FROM sys.dm_exec_describe_first_result_set('EXEC #usp_dynamic_output', NULL, 0)
GO
DROP PROCEDURE #usp_dynamic_output;

You'll see it only outputs a single blank row.

Answer 2

Take a look at this:

tSQLt.AssertResultSetsHaveSameMetaData [@expectedCommand = ] 'expected command'
                                     , [@actualCommand = ] 'actual command'

(documentation)

I believe that provides what you're looking for.