Find the parameter names of a stored procedure

问题:

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

I have found this Link but it is not for Microsoft SQL Server 2008.

回答1:

To get names only you can use this query:

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

To get more detailed info (name, type and length of 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')

回答2:

On top of what Marek stated, you can also retrieve them programatically using the DeriveParameters method in the .NET library: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx

回答3:

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

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

One way is to use the sys.parameters table. This can be optionally joined to types. Below is joined to 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

A 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 get you where you want to go.