如何获取存储过程参数详细信息? [英] 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.

在sys.objects中仅存在有关该过程的常见详细信息.在sys.sql_modules中，我可以提取过程的整个SQL文本.

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 Server管理工作室)我能够提取关于使用在表格视图的参数的信息选择过程名时.我希望可以在某些地方以这种方式提取输入参数的详细信息.

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.

推荐答案

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

这篇关于如何获取存储过程参数详细信息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！