Get column names/types returned from a stored proc

问题:

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...

回答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.

回答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

回答3:

My way of doing this: Edit the stored procedure to have an INTO clause:

Change

Select * from tablename

to

Select * INTO _tablename FROM tablename

This creates a table in the database. Then, use SELECT * FROM INFORMATION_SCHEMA WHERE TABLE_NAME = '_tablename'

Don't forget to undo the modification to the sproc.