How to get stored procedure parameters details?

问题:

Where can I find information about stored procedure parameters? In my situation I need to know only the input parameters of given store procedure.

In the sys.objects there is only common details about the procedure. In sys.sql_modules I can extract the whole SQL text of a procedure.

As (in SQL Server Management studio) I am able to extract information about the parameters in tabular view using ALT+F1 when selecting the procedure name. I hope there is some place from which I can extract input parameters details in that way.

回答1:

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters where object_id = object_id('MySchema.MyProcedure')

回答2:

select * from sys.parameters 
inner join sys.procedures on parameters.object_id = procedures.object_id 
inner join sys.types on parameters.system_type_id = types.system_type_id AND parameters.user_type_id = types.user_type_id
where procedures.name = 'sp_name'

回答3:

There are the system tables, like sys.objects or sys.sysobjects.

Or you could also look at INFORMATION_SCHEMA, specifically INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.ROUTINE_COLUMNS.

Because it is in the ANSI-standard INFORMATION_SCHEMA, there are less SQL Server specific quirks. IMHO it is easier to understand for most things.

回答4:

For a supplied procedure name, the following query lists all of its parameters and their order along with their type, whether the type is nullable and the type's length (for use with VARCHAR, etc.)

Replace procedure_name with the name of your procedure.

DECLARE @ProcedureName VARCHAR(MAX) = 'procedure_name'

SELECT
    pa.parameter_id AS [order]
    , pa.name AS [name]
    , UPPER(t.name) AS [type]
    , t.is_nullable AS [nullable] 
    , t.max_length AS [length] 
FROM 
    sys.parameters AS pa 
    INNER JOIN sys.procedures AS p on pa.object_id = p.object_id 
    INNER JOIN sys.types AS t on pa.system_type_id = t.system_type_id AND pa.user_type_id = t.user_type_id
WHERE 
    p.name = @ProcedureName
ORDER BY 
    t.is_nullable DESC

回答5:

It Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

SELECT *  
FROM sys.parameters  
WHERE object_id = object_id('SchemaName.ProcedureName')

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-parameters-transact-sql?view=sql-server-2017

回答6:

The following Query worked for me:

SELECT * FROM sys.parameters sp1, sys.procedures sp2 WHERE sp1.object_id = sp2.object_id

For more specific result with parameter order:

SELECT * FROM sys.parameters sp1, sys.procedures sp2, sys.types st WHERE sp1.object_id = sp2.object_id AND sp2.name = 'usp_nameofstoredprocedure' AND sp1.user_type_id = st.user_type_id ORDER BY sp1.parameter_id asc;

回答7:

SELECT *  
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_NAME='proc_name' 
ORDER BY ORDINAL_POSITION 

(tested with MSSQL 2014)

回答8:

Probably a little late , but since the search term Get parameters for all stored procedure on SQL on google, landed me this page, I will post that solution (which is also bit different from other answers in terms of join)

 Select PROCS.name As StoredProcName,TYPE_NAME(user_type_id) As ParameterType,PARAMS.name As Params from sys.procedures PROCS
JOIN sys.parameters PARAMS WITH(NOLOCK) ON PROCS.object_id = PARAMS.object_id
Order by PROCS.object_id

回答9:

select t1.[name] as [SP_name],t2.[name] as [Parameter_name],
t3.[name] as [Type],t2.[Length],t2.colorder as [Param_order]
from sysobjects t1
inner join syscolumns t2 on t1.[id]=t2.[id]
inner join systypes t3 on t2.xtype=t3.xtype
where t1.[name]='My_StoredProc_Name'
order by [Param_order]