?? 查詢存儲(chǔ)過程參數(shù)定義.sql
字號(hào):
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
/*--查詢存儲(chǔ)過程的參數(shù)定義
查詢存儲(chǔ)過程,用戶定義函數(shù)的參數(shù)定義
--鄒建 2005.05(引用請(qǐng)保留此信息)--*/
/*--調(diào)用示例
EXEC sp_PROC_Params
@procedure_name='sp%',
@group_number=0,
@operator='%'
--*/
CREATE PROC sp_PROC_Params
@procedure_name sysname, --存儲(chǔ)過程或者用戶定義函數(shù)名
@group_number int=1, --存儲(chǔ)過程的組號(hào),必須在0到32767之間,0表示顯示該存儲(chǔ)過程組的所有參數(shù)
@operator nchar(2)=N'=' --查找對(duì)象的運(yù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 ''<Returns>''
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 ''<Returns>''
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
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -