254.查询存储过程的参数定义_查询存储过程参数-优快云博客

本文介绍了一个SQL Server的存储过程，用于查询存储过程和用户定义函数的参数定义。通过执行此存储过程，可以获取包括存储过程名称、所有者、参数ID、参数名称、类型和方向等详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE master

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PROC_Params]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PROC_Params]
GO

/*--查询存储过程的参数定义
	
	查询存储过程,用户定义函数的参数定义
	
*/

/*--调用示例

	EXEC sp_PROC_Params 
		@procedure_name='sp%',
		@group_number=0,
		@operator='%'
--*/
CREATE PROC sp_PROC_Params
@procedure_name sysname,            --存储过程或者用户定义函数名
@group_number   int=1,              --存储过程的组号,必须在0到32767之间,0表示显示该存储过程组的所有参数
@operator       nchar(2)=N'='       --查找对象的运算符
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT
		PorcedureName=CASE 
			WHEN o.xtype IN(''P'',''X'')
			THEN QUOTENAME(o.name)+N'';''+CAST(c.number as varchar)
			WHEN USER_NAME(o.uid)=''system_function_schema''
				AND o.xtype=''FN''
			THEN o.name
			WHEN  USER_NAME(o.uid)=''system_function_schema''
			THEN ''::''+o.name
			WHEN o.xtype=''FN''
			THEN QUOTENAME(USER_NAME(o.uid))+N''.''+QUOTENAME(o.name)
			ELSE QUOTENAME(o.name) END,
		Owner=USER_NAME(o.uid),
		GroupNumber=c.number,
		ParamId=c.colid,
		ParamName=CASE 
			WHEN o.xtype=''FN'' AND c.colid=0 THEN ''''
			ELSE c.name END,
		Type=QUOTENAME(t.name)+CASE 
			WHEN t.name IN (''decimal'',''numeric'')
			THEN N''(''+CAST(c.prec as varchar)+N'',''+CAST(c.scale as varchar)+N'')''
			WHEN t.name IN (''float'',''real'')
				OR t.name like ''%char''
				OR t.name like ''%binary''
			THEN N''(''+CAST(c.prec as varchar)+N'')''
			ELSE '''' END,
		Orientation=CASE 
			WHEN o.xtype=''FN'' AND c.colid=0 THEN ''''
			ELSE N''Input''
				+CASE WHEN c.isoutparam=1 THEN ''/Output'' ELSE '''' END
			END
	FROM sysobjects o,syscolumns c,systypes t
	WHERE o.id=c.id
		AND c.xusertype=t.xusertype
		AND o.name'
	+CASE 
		WHEN @operator IN('=','>','>=','!>','<','<=','!<','<>','!=')
		THEN @operator+QUOTENAME(@procedure_name,'''')
		WHEN @operator='IN'
		THEN @operator+N' IN('+QUOTENAME(@procedure_name,'''')+')'
		WHEN @operator IN('LIKE','%')
		THEN ' LIKE '+QUOTENAME(@procedure_name,'''')
		ELSE '='+QUOTENAME(@procedure_name,'''')
	END
	+N' 
		AND(('
	+CASE 
		WHEN @group_number BETWEEN 1 AND 32767 
		THEN N'c.number='+CAST(@group_number as varchar)
		WHEN @group_number=0
		THEN N'1=1'
		ELSE N'c.number=1'
	END+N' AND o.xtype IN(''P'',''X'')) 
		OR (c.number=0 AND o.xtype=''FN'')
		OR (c.number=1 AND o.xtype IN(''IF'',''TF'')))'
EXEC sp_executesql @sql
GO