Q:

Find Data Types and Column Names For Stored Procedure Result in SQL Server

I am using Dapper as an ORM tool to consume a SQL Server database in C# code.  To do this, I am creating strongly-typed classes that mirror the database structure so that it is easy to get objects back and forth between C# and SQL Server.
To quickly model C# classes based on SQL Server table definitions (and to write unit tests that ensure that the data layer and database are in sync), I use queries like this:
SELECT 
    SchemaName = c.table_schema, TableName = c.table_name, 
    ColumnName = c.column_name, DataType = data_type, 
    MaxLength = ISNULL(c.CHARACTER_MAXIMUM_LENGTH, -1) 
FROM 
    information_schema.columns c 
INNER JOIN 
    information_schema.tables t ON c.table_name = t.table_name 
                                AND c.table_schema = t.table_schema 
                                AND t.table_type = 'BASE TABLE' 
ORDER BY 
    SchemaName, TableName, ordinal_position;

This returns each schema and table name with each of its columns and data types, which makes things easy for most CRUD-style operations:

The problem I have now is creating model classes to consume certain stored procedures, which join many tables and use aliases for a lot of the columns (eg, join to the employee table twice, alias the first set of employee details as ManagerName, ManagerEmail and the second set as WorkerName, WorkerEmail, etc.)  Some of these procedures return hundreds of columns, and I am having to wade through table definitions to see which columns need to be modeled as Int16/Int32/Int64, etc., plus the procedures are not set in stone at this point, and I don't want to have to manually audit these things to make sure they are in sync if I can help it.
So, my question is: does SQL Server provide a way to see "metadata" about a stored procedure's result set (ie, the column names and data types that a procedure will return)?  If not, is there a setting in SSMS that will display a data type alongside the column name, or some other creative little hack that will make this task easier?

A:

Depending on how your procedure is written you may be able to get what you need from sp_describe_first_result_set.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql
You would need to parse out result set but easier then manually hunting all of the columns.

