Как получить подробную информацию о параметрах хранимой процедуры?
Где я могу найти информацию о параметрах хранимой процедуры? В моей ситуации мне нужно знать только входные параметры данной процедуры хранения.
в sys.objects Есть только общие детали о процедуре. В sys.sql_modules Я могу извлечь весь текст SQL процедуры.
Как (в SQL Server Management studio) я могу извлечь информацию о параметрах в табличном виде, используя ALT+F1 при выборе имени процедуры. Я надеюсь, что есть место, откуда я смогу извлечь детали входных параметров таким образом.
8 ответов
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')
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'
Для указанного имени процедуры в следующем запросе перечислены все ее параметры и их порядок, а также их тип, тип NULL и длина типа (для использования с VARCHAR и т. Д.).
замещать procedure_name с названием вашей процедуры.
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
SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='proc_name'
ORDER BY ORDINAL_POSITION
(протестировано с MSSQL 2014)
Информационные схемы - это стандарт SQL ISO. В представлении схемы информации PARAMETERS отображается список параметров для пользовательских функций и хранимых процедур в текущей или указанной базе данных. Это тот, который я использую, чтобы получить список всех параметров для всех процедур:
SELECT SPECIFIC_NAME, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
ORDER BY SPECIFIC_NAME
Возможно, немного поздно, но так как поисковый запрос Get parameters for all stored procedure on SQL на Google, приземлил мне эту страницу, я опубликую это решение (которое также немного отличается от других ответов с точки зрения присоединения)
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
Есть системные таблицы, вроде sys.objects или же sys.sysobjects,
Или вы также можете посмотреть на INFORMATION_SCHEMAконкретно INFORMATION_SCHEMA.ROUTINES а также INFORMATION_SCHEMA.ROUTINE_COLUMNS,
Потому что это в стандарте ANSI INFORMATION_SCHEMAЕсть меньше специфических особенностей SQL Server. ИМХО это легче понять для большинства вещей.
Содержит строку для каждого параметра объекта, который принимает параметры. Если объект является скалярной функцией, существует также одна строка, описывающая возвращаемое значение. Эта строка будет иметь значение параметра_идентификатора 0.
SELECT *
FROM sys.parameters
WHERE object_id = object_id('SchemaName.ProcedureName')
Следующий запрос работал для меня:
SELECT * FROM sys.parameters sp1, sys.procedures sp2 WHERE sp1.object_id = sp2.object_id
Для более конкретного результата с параметром 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;
Расширение ответа Раджа выше
;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;
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]