How to obtain a list of columns returned by a stored procedure in SQL Server?

Question

In SQL Server, how can I query the system tables to return all the column names of a stored procedure? For a view, I know I can use sp_columns or sys.columns. But when I use those against my stored procedure, it returned 0 rows.

I have tried the following:

EXEC sp_columns MyStoredProc;

and:

SELECT * 
FROM sys.columns 
WHERE [object_id] = OBJECT_ID('MyStoredProc');

Both returned nothing.

Thanks in advance

Answer 1

If you are asking for SQL 2012 or later, you can use

exec sp_describe_first_result_set N'your-stored-procedure-name'

Note: This will return the first result set returned by your stored procedure.

MSDN