I am developing an application in PHP connected with SQL Server 2008 R2, my requirement is getting list of input / output parameters (METADATA) from procedure(s) and generating run time forms on the screen. I have around fifty scenarios e.g. get order details, get pending orders etc.
So instead of developing those 50 screens,I have decided to generate these forms on run time. Now I Googled my problem a bit and I came to know this feature is given in SQL Server 2012 by using sp_describe_first_result_set. Reference: link
Is there a way to achieve this in SQL Server 2008?
The next query returns a list of stored procedures and their parameters from the current database:
select pr.object_id [procedure_id]
, pr.name [procedure_name]
, p.parameter_id
, p.name [parameter_name]
, p.is_output
from sys.parameters p
join sys.procedures pr on p.object_id = pr.object_id
order by pr.object_id, p.parameter_id
You can use SET FMTONLY to execute the stored procs that have result sets with only meta data being returned. This returns only metadata to the client. Can be used to test the format of the response without actually running the query. Actually, in MSDN, it says don't use this because it has been replaced with sp_describe_first_result_set (in 2012) so there you go.
To get metadata about the parameters to the stored proc, your best bet is to use
SELECT * FROM INFORMATION_SCHEMA.Parameters