-3

I have written a procedure but I want to figure out what columns (fields) in the output without running the process?

  • 1
    Welcome to DBA.SE! Am I correct in assuming that the procedure is dynamic? Otherwise, you should know what it returns as you wrote it. – Mr.Brownstone Jun 23 at 9:52
  • There's no way to do this in a reliable way, since SQL Server allows for things like conditional logic and dynamic SQL in a stored procedure. Just keep that in mind, for whatever method you run into... – Tibor Karaszi Jun 25 at 7:02
2

Currently you've tagged both and .

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 sp_helptext

  • The sp_describe_first_result_set process can not support dynamic queries. – Amir Soori Jun 25 at 13:18
2

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');
  • I'd been holding off on upvoting this since it executes and commits the underlying proc (contrary to OP spec), but it's just so darn useful I had to & I keep coming back to it! 😜+1 – Peter Vandivier Jul 9 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.