I need to retrieve a list of columns returned from a stored procedure on MS SQL which may be 2005 or higher. I also need to do this without passing any input parameters even if they are required. I had read about using the SET FMTONLY ON but I still have to pass the parameters. I just need to select the column names so they can be used for generating a report. The user will select a stored procedure which I can already get a list of but I want to select one and see the columns returned.
Because the columns returned from the stored proc can depend on the parameters passed, prior to SQL 2012, you would need to pass the parameters in order to get the column headers. If you are on SQL 2012, you could try sys.dm_exec_describe_first_result_set_for_object : http://msdn.microsoft.com/en-us/library/ff878236.aspx
Editing: Sorry russell, didn't see your reply before I hit "post" button.
Thanks. I'm writing an app that may be used with 2012 or 2008. I wish there was some way of executing the stored procedure with the parameters as null. I don't care for the data returned, only the columns returned. I'm wanting the user to select the columns of data to include in a table. I guess as last resort I can prompt the user to enter data for the parameters. Is there a way to get a list of parameters for the stored procedure? I will try searching on this now.
Okay, I found a routine shown below that will list the parameters for a stored procedure. So now what do I do to get the columns only that are returned and no data? Should I set the FMTONLY to ON and call the stored procedure? I believe that causes it not to return any data. How do I extract the column headers from the empty recordset?
SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'sproc name here'
I am trying to get a list of the columns returned from the stored procedure. I am then going to let the user select which ones they want to be shown in a table for a report. I am writing my own dashboard app for someone and would like for them to use existing stored procedures.
They are selecting the stored procedure from a list of all on their server. So I can't do that. And I'm trying to do this without creating new stored procedures. I can call SQL to get info but I'm not going to create a new one. So just running the selected sproc to get the column names is the only way.