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

How to get stored procedure parameters details?

提问by gotqn

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.objectsthere is only common details about the procedure. In sys.sql_modulesI can extract the whole SQL text of a procedure.

在sys.objects只有有关程序常见的细节。在sys.sql_modules我可以提取过程的整个 SQL 文本。

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

作为（在 SQL Server Management Studio 中），我能够ALT+F1在选择过程名称时使用表格视图提取有关参数的信息。我希望有一些地方可以以这种方式提取输入参数的详细信息。

回答by Raj

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')

回答by Ahmad Behjati

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'

回答by WonderWorker

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

对于提供的过程名称，以下查询列出了它的所有参数及其顺序以及它们的类型和类型的长度（用于 VARCHAR 等）

Replace procedure_namewith the name of your procedure.

替换procedure_name为您的程序名称。

DECLARE @ProcedureName VARCHAR(MAX) = 'procedure_name'

SELECT
    pa.parameter_id AS [order]
    , pa.name AS [name]
    , UPPER(t.name) AS [type]
    , 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

回答by Naveen Kumar V

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;

回答by Paul Draper

There are the system tables, like sys.objectsor sys.sysobjects.

有系统表，例如sys.objects或sys.sysobjects。

Or you could also look at INFORMATION_SCHEMA, specifically INFORMATION_SCHEMA.ROUTINESand INFORMATION_SCHEMA.ROUTINE_COLUMNS.

或者您也可以查看INFORMATION_SCHEMA，特别是INFORMATION_SCHEMA.ROUTINES和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 标准INFORMATION_SCHEMA，所以 SQL Server 特定的怪癖较少。恕我直言，大多数事情更容易理解。

回答by Santhana

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.

它为接受参数的对象的每个参数包含一行。如果对象是标量函数，则还有一行描述返回值。该行的 parameter_id 值为 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

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

回答by Simsons

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

可能有点晚了，但是由于Get parameters for all stored procedure on SQLgoogle 上的搜索词让我找到了这个页面，我将发布该解决方案（这也与加入方面的其他答案略有不同）

 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

回答by AeyJey

An extension of Raj's answer above

Raj 上面回答的扩展

;WITH CTE
 AS (SELECT OBJECT_NAME(OBJECT_ID) AS  sql_module_name
           ,CASE
                WHEN OBJECTPROPERTY(OBJECT_ID,'IsProcedure') = 1 THEN 'Stored Procedure'
                WHEN OBJECTPROPERTY(OBJECT_ID,'IsScalarFunction') = 1 THEN 'Scalar Function'
                WHEN OBJECTPROPERTY(OBJECT_ID,'IsTableFunction') = 1 THEN 'Table Function'
            END AS                     sql_module_type
           ,parameter_id AS            parameter_order
           ,name AS                    parameter_name
           ,is_nullable AS             parameter_is_nullable_flag
           ,is_output AS               parameter_is_output_flag
           ,TYPE_NAME(user_type_id) AS parameter_type
           ,max_length AS              parameter_length
           ,CASE
                WHEN TYPE_NAME(system_type_id) = 'uniqueidentifier' THEN precision
                ELSE OdbcPrec
                     (system_type_id,max_length,precision
                     )
            END AS                     parameter_precision
           ,OdbcScale
            (system_type_id,scale
            ) AS                       parameter_scale
     FROM   sys.parameters)
 SELECT DENSE_RANK() OVER(
        ORDER BY sql_module_type
                ,sql_module_name ASC) AS group_id
       ,sql_module_name
       ,sql_module_type
       ,parameter_order
       ,parameter_name
       ,parameter_is_nullable_flag
       ,parameter_is_output_flag
       ,parameter_type
       ,parameter_length
       ,parameter_precision
       ,parameter_scale
 FROM   CTE
 ORDER BY sql_module_type
         ,sql_module_name
         ,parameter_order;

回答by Logos Sohl

Information Schemas are are ISO standard SQL. The PARAMETERS information schema view displays a list of parameters for user-defined functions and stored procedures in the current or specified database. This is one I use to get a list of all parameters for all procedures:

信息模式是 ISO 标准 SQL。PARAMETERS 信息架构视图显示当前或指定数据库中用户定义函数和存储过程的参数列表。这是我用来获取所有过程的所有参数列表的一个：

SELECT          SPECIFIC_NAME,  PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE 
FROM            INFORMATION_SCHEMA.PARAMETERS
ORDER BY        SPECIFIC_NAME

回答by bertrand gajac

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

(tested with MSSQL 2014)

（使用 MSSQL 2014 测试）