如何获取存储过程参数的详细信息?

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.

在系统。对象关于过程只有一般的细节。在系统。我可以提取一个过程的整个SQL文本。

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 get extract them in that way.

As(在SQL Server Management studio中)在选择过程名时，我可以使用ALT+F1提取表视图中参数的信息，我希望有某个地方可以这样提取它们。

5 个解决方案

#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

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

有系统表，比如sys。对象或sys.sysobjects。

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

或者你也可以看看INFORMATION_SCHEMA，尤其是INFORMATION_SCHEMA。例程和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.

因为它位于ANSI-standard INFORMATION_SCHEMA中，所以很少有SQL Server特有的怪癖。在大多数情况下，IMHO更容易理解。

#3

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'

#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.)

对于提供的过程名，下面的查询列出了它的所有参数、它们的顺序以及它们的类型、类型是否为空以及类型的长度(用于VARCHAR，等等)。

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

-2

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]

#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