Find stored procedure parameter names

Question

I am using Microsoft SQL Server 2008. I have a stored procedure. Is there a simple query that I can execute that will give me parameter names?

I found a link, but not for Microsoft SQL Server 2008.

Answer 1

To get only names, you can use this query:

SELECT name FROM sys.parameters WHERE object_id = OBJECT_ID('YourProcedureName') 

To get more detailed information (name, type and length of the parameter):

 SELECT p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength FROM sys.parameters AS p JOIN sys.types AS t ON t.user_type_id = p.user_type_id WHERE object_id = OBJECT_ID('YourProcedureName') 

Answer 2

In addition to what Marek said, you can also program them using the DeriveParameters method in the .NET library: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx p>

Answer 3

Check out my blog for files and database objects. http://craftydba.com/?p=2901

I have a stored procedure called SP_STORE_PRIMES in my database [MATH].

One way is to use the sys.parameters table. This may optionally be attached to types. Below joins sys.objects.

 -- Parameters to SP & FN select o.name, p.* from sys.parameters p join sys.objects o on p.object_id = o.object_id where is_ms_shipped = 0 go 

An older system stored procedure is sp_sproc_columns.

 -- Older system stored proc - show all parameters to one sp_sproc_columns @procedure_name = 'SP_STORE_PRIMES' go 

Both ways will help you get where you want to go.