I have written a procedure but I want to figure out what columns (fields) in the output without running the process?
If 2012 is correct, you can use
sp_describe_first_result_set subject to a few restrictions.
If your procedure is not eligible for
sp_describe_first_result_set or you are running a version that does not support it, you will have to examine the source code - my preferred method of extracting this is the
object_definition() function, though many people prefer
OPENROWSET returns a table from an arbitrary query. That query can
exec some_procedure. The results can be used to define a new table using the
select .. into syntax. This new table's metadata can be examined to find the definition of the SP's output.
drop procedure if exists SomeProc; go create or alter procedure SomeProc as begin select a = CONVERT(bit, 1), b = CONVERT(int, 2) end go drop table if exists SomeTable; go select * into SomeTable from openrowset ( 'SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC SomeProc' ); go select * from SomeTable; go select * from sys.all_columns where object_id = OBJECT_ID('SomeTable');