An Overlooked Stored Procedure since SQL2012 - sp_describe_first_result_set

As a database developer, we create stored procedures for application developers to call them in their application code, most of the time they ask us to provide a document which list out the input parameters and result columns. Where listing stored procedure parameters are very easy by copying the parameters from the stored procedure definition, extracting result columns from the stored procedure body may be not that easy, especially there may be a SELECT * and joining multiple tables in the output statement. Starting with SQL Server 2012, there's a built-in stored procedure, sp_describe_first_result_set, which returns the metadata for the first possible result set of a Transact-SQL batch. Here is an example:


USE StackOverflow2010
GO
-- =============================================
-- Author:  Peter Lee
-- Create date: 20180102
-- Description: SP Get User Posts
-- =============================================
CREATE OR ALTER PROCEDURE uspGetUserPosts
 @DisplayName nvarchar(40)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @DisplayName AS UserName, U.Id AS UserId, U.CreationDate AS UserCreationDate,
  U.Reputation, P.CreationDate AS PostCreationDate, P.Title
 FROM Users AS U JOIN Posts AS P ON U.Id = P.OwnerUserId
 WHERE U.DisplayName = @DisplayName
 ORDER BY PostCreationDate DESC, Title
END
GO
EXEC dbo.uspGetUserPosts 'Anonymous User';
GO
EXEC sp_describe_first_result_set N'EXEC dbo.uspGetUserPosts ''Anonymous User''';
GO


The sp_describe_first_result_set stored procedure will tell you the metadata for the result set of your stored procedure, including the column name, data type, ordinal, columns in ORDER BY list, etc.