¿Cómo obtener los detalles de los parámetros de procedimiento almacenados?

¿Dónde puedo encontrar información sobre los parámetros de procedimiento almacenados? En mi situación necesito saber solo los parámetros de entrada del procedimiento de almacenamiento dado.

En el sys.objects solo hay detalles comunes sobre el procedimiento. En sys.sql_modules puedo extraer todo el texto SQL de un procedimiento.

Como (en SQL Server Management studio) soy capaz de extraer información sobre los parámetros en la vista tabular usando ALT+F1 al seleccionar el nombre del procedimiento, espero que haya algún lugar desde el que obtenga extráigalos de esa manera.

Author: Aaron Bertrand, 2013-11-21
Source

6 answers

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')
 52
Author: Raj,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-11-21 09:02:51 
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'
 2
Author: Ahmad Behjati,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-26 11:46:30

Están las tablas del sistema, como sys.objects o sys.sysobjects.

O también podrías mirar INFORMATION_SCHEMA, específicamente INFORMATION_SCHEMA.ROUTINES y INFORMATION_SCHEMA.ROUTINE_COLUMNS.

Debido a que está en el estándar ANSI INFORMATION_SCHEMA, hay menos peculiaridades específicas de SQL Server. En mi humilde opinión es más fácil de entender para la mayoría de las cosas.

 1
Author: Paul Draper,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-11-21 08:51:49

Para un nombre de procedimiento suministrado, la siguiente consulta lista todos sus parámetros y su orden junto con su tipo, si el tipo es nullable y la longitud del tipo (para usar con VARCHAR, etc.).)

Reemplace procedure_name por el nombre de su procedimiento.

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
 0
Author: Knickerless-Noggins,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-16 15:58:52

La siguiente consulta funcionó para mí:

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

Para un resultado más específico con el orden de los parámetros:

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;
 0
Author: Naveen Kumar V,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-10-01 06:35:09 
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]
 -2
Author: user3004228,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-11-21 08:58:23