Get column names/types returned from a stored procedure

Question

Is there a way via metadata (Information_Schema, perhaps?) to get a list of the columns a sproc will return? I'm trying to automate some code generation and that would help tremendously...

Answer 1

Unless you're prepared to parse the contents of ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, then your best bet will be to execute the procedures, and read the column information from the records returned.

In .NET you can do this by reading the results of the stored procedure into a DataTable and querying the Columns property.

The reason there's no easy way to do this is a stored procedure could potentially return different result sets based on the parameters. There's no fixed result set format like there is with user defined functions.

Edit

As mentioned in the other answer, you will need to use SET FMTONLY ON to ensure no data is returned. There are some situations where SET FMTONLY won't work, e.g. when using #temp tables in your stored procedures, but there is a workaround.

Answer 2

I just ran Profiler to see how Visual Studio does this for the strongly typed dataset drag and drop.

This is the code it sent.

 SET NO_BROWSETABLE ON; 
 SET FMTONLY ON;

exec dbo.aspnet_Roles_GetAllRoles @ApplicationName=NULL

So I presume there might not be any "more official" way of doing it.

Obviously you would need to bear in mind that a single stored procedure might return multiple result sets or different result sets dependant on the parameters passed.

For people on 2012+ another approach might be to use sp_describe_first_result_set