254.查询存储过程的参数定义

一时失言乱红尘 2023-12-27 06:20 27阅读 0赞

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