t-sqlstored-proceduressql-server-2012detectcolumn-types

T-SQL Detect the type of the columns that store procedure returns

I am not going to deep into the details but I need a way to determined the type of the return columns by store procedure.

Is there a way to achieved that and I do not look for a solution that works with specified procedure - I should be able to detect this in other procedure that receive as parameter other procedure's name.

I am using SQL Server 2012.

Solution

  • In SQL Server 2012 you can use sp_describe_first_result_set

    As the name implies this returns metadata about the first result set only returned by a stored procedure. Example below.

    CREATE PROC Foo
AS
SELECT *
FROM sys.objects

GO

EXEC sp_describe_first_result_set @tsql = N'Foo'