List parameters for Stored Procedures

Take a look at the catalog views sys.procedures and sys.parameters.



On 7/10/08 7:31 PM, in article #q3ljUu4IHA.5012@TK2MSFTNGP02.phx.gbl,
"tshad" wrote:

> I am trying to get a list of stored procedures (not system stored
> procedures) along with there parameters.
>
> Is this possible?
>
> Thanks,
>
> Tom
>
>
 >> Stay informed about: List parameters for Stored Procedures 

On Jul 10, 8:54 pm, SQL Menace wrote:
> You can also use the INFORMATION_SCHEMA.PARAMETERS view
>
> SELECT *
> FROM INFORMATION_SCHEMA.PARAMETERS
> WHERE SPECIFIC_NAME='YourProcName'
>
> Denis The SQL Menacehttp://www.lessthandot.com/http://sqlservercode.blogspot.comhttp...sqlblog
>
> On Jul 10, 7:31 pm, "tshad" wrote:
>
> > I am trying to get a list of stored procedures (not system stored
> > procedures) along with there parameters.
>
> > Is this possible?
>
> > Thanks,
>
> > Tom

OP said he wants "to get a list of stored procedures (not system
stored procedures) along with there parameters. " So, I think he
would like to have a result set like this:

name param1 param2 param3 ....
------------------------------------------------------
proc1 param1 param2 param 3 ...
proc2 param1 null null
proc3 null null null
proc4 param1 param2 null

I think this is possible by querying the sysobjects table and the
syscolumns table since sysobjects has the procedure names and
syscolumns has parameters of procedures.

I am not sure how to determine the maximum number of columns for this
result set, since theoretically a stored procedure can have 0 to 2100
parameters.
 >> Stay informed about: List parameters for Stored Procedures 

On Jul 11, 4:44 pm, "Aaron Bertrand [SQL Server MVP]"
wrote:
> > OP said he wants "to get a list of stored procedures (not system
> > stored procedures) along with there parameters. "  So, I think he
> > would like to have a result set like this:
>
> > name  param1 param2 param3 ....
> > ------------------------------------------------------
> > proc1  param1 param2 param 3 ...
>
> Why would you assume that the result set has to go across like that?  I
> would rather read a list down, like:
>
> proc1   param1
> proc1   param2
> proc1   param3
>
> > I am not sure how to determine the maximum number of columns for this
> > result set, since theoretically a stored procedure can have 0 to 2100
> > parameters.
>
> This is not really the way we query data.  You can build a delimited string
> and return everything in a single column, but if this is even the way the
> result is desired, it is usually best to perform such manipulation on the
> client.

Thanks. I too intuitively think that the result set I guessed would
look weird to have more nulls than actual meaningful data. I think to
put the list of parameters in a delimited string in one column for
each procedure is a good idea. However, we are only guessing, not
sure what format OP really wants.
 >> Stay informed about: List parameters for Stored Procedures 

> OP said he wants "to get a list of stored procedures (not system
> stored procedures) along with there parameters. " So, I think he
> would like to have a result set like this:
>
> name param1 param2 param3 ....
> ------------------------------------------------------
> proc1 param1 param2 param 3 ...

Why would you assume that the result set has to go across like that? I
would rather read a list down, like:

proc1 param1
proc1 param2
proc1 param3

> I am not sure how to determine the maximum number of columns for this
> result set, since theoretically a stored procedure can have 0 to 2100
> parameters.

This is not really the way we query data. You can build a delimited string
and return everything in a single column, but if this is even the way the
result is desired, it is usually best to perform such manipulation on the
client.
 >> Stay informed about: List parameters for Stored Procedures 

SELECT
p.parameter_id,
p.name,
st.name AS TypeName,
p.is_output
FROM
sys.objects o
INNER JOIN sys.parameters p ON p.object_id = o.object_id
INNER JOIN sys.systypes st ON st.xtype = p.system_type_id
WHERE
o.name = '<MY SP NAME>';

> On Thursday, July 10, 2008 7:31 PM tshad wrote:

> I am trying to get a list of stored procedures (not system stored
> procedures) along with there parameters.
>
> Is this possible?
>
> Thanks,
>
> Tom


>> On Thursday, July 10, 2008 8:12 PM Aaron Bertrand [SQL Server MVP] wrote:

>> Take a look at the catalog views sys.procedures and sys.parameters.


>>> On Friday, July 11, 2008 7:35 AM SQL Menace wrote:

>>> You can also use the INFORMATION_SCHEMA.PARAMETERS view
>>>
>>> SELECT *
>>> FROM INFORMATION_SCHEMA.PARAMETERS
>>> WHERE SPECIFIC_NAME=3D'YourProcName'
>>>
>>>
>>> Denis The SQL Menace
>>> http://www.lessthandot.com/
>>> http://sqlservercode.blogspot.com
>>> http://sqlblog.com/blogs/denis_gobo/default.aspx
>>>
>>> On Jul 10, 7:31=A0pm, "tshad" wrote:


>>>> On Friday, July 11, 2008 4:44 PM Aaron Bertrand [SQL Server MVP] wrote:

>>>> Why would you assume that the result set has to go across like that? I
>>>> would rather read a list down, like:
>>>>
>>>> proc1 param1
>>>> proc1 param2
>>>> proc1 param3
>>>>
>>>>
>>>> This is not really the way we query data. You can build a delimited string
>>>> and return everything in a single column, but if this is even the way the
>>>> result is desired, it is usually best to perform such manipulation on the
>>>> client.


>>>>> On Tuesday, July 15, 2008 8:16 PM Author wrote:

>>>>> On Jul 10, 8:54=A0pm, SQL Menace wrote:
>>>>> pot.comhttp://sqlblog.com/blogs/denis_gobo/default.aspx
>>>>>
>>>>> OP said he wants "to get a list of stored procedures (not system
>>>>> stored procedures) along with there parameters. " So, I think he
>>>>> would like to have a result set like this:
>>>>>
>>>>> name param1 param2 param3 ....
>>>>> ------------------------------------------------------
>>>>> proc1 param1 param2 param 3 ...
>>>>> proc2 param1 null null
>>>>> proc3 null null null
>>>>> proc4 param1 param2 null
>>>>>
>>>>> I think this is possible by querying the sysobjects table and the
>>>>> syscolumns table since sysobjects has the procedure names and
>>>>> syscolumns has parameters of procedures.
>>>>>
>>>>> I am not sure how to determine the maximum number of columns for this
>>>>> result set, since theoretically a stored procedure can have 0 to 2100
>>>>> parameters.


>>>>>> On Tuesday, July 15, 2008 8:16 PM Author wrote:

>>>>>> On Jul 11, 4:44=A0pm, "Aaron Bertrand [SQL Server MVP]"
>>>>>> wrote:
>>>>>> ring
>>>>>>
>>>>>> Thanks. I too intuitively think that the result set I guessed would
>>>>>> look weird to have more nulls than actual meaningful data. I think to
>>>>>> put the list of parameters in a delimited string in one column for
>>>>>> each procedure is a good idea. However, we are only guessing, not
>>>>>> sure what format OP really wants.
 >> Stay informed about: List parameters for Stored Procedures