–1\u3001\u67e5\u770b\u6240\u6709\u5b58\u50a8\u8fc7\u7a0b\u4e0e\u51fd\u6570
exec sp_stored_procedures
\u6216\u8005
select * from dbo.sysobjects where OBJECTPROPERTY(id, N’IsProcedure’) = 1 order by name
–2\u3001\u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u7684\u5185\u5bb9
select text from syscomments where id=object_id(‘\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0’)
— \u6216\u8005\u7528
sp_helptext \u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0 <\/p>\n
–3\u3001\u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u7684\u53c2\u6570\u60c5\u51b5
select ‘\u53c2\u6570\u540d\u79f0’ = name,
‘\u7c7b\u578b’ = type_name(xusertype),
‘\u957f\u5ea6’ = length,
‘\u53c2\u6570\u987a\u5e8f’ = colid,
‘\u6392\u5e8f\u65b9\u5f0f’ = collation
from syscolumns
where id=object_id(‘\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0’) <\/p>\n
–\u6216\u8005 <\/p>\n
–\u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u53c2\u6570\u4fe1\u606f\uff1a
–\u5982\u679c\u8fd4\u56de\u503c>1\uff0c\u5219\u6709\u53c2\u6570\u3002\u5426\u5219\u65e0
Create PROC sp_PROC_Params
@procedure_name sysname , –\u5b58\u50a8\u8fc7\u7a0b\u6216\u8005\u7528\u6237\u5b9a\u4e49\u51fd\u6570\u540d
@group_number int=1 , –\u5b58\u50a8\u8fc7\u7a0b\u7684\u7ec4\u53f7,\u5fc5\u987b\u57280\u523032767\u4e4b\u95f4,0\u8868\u793a\u663e\u793a\u8be5\u5b58\u50a8\u8fc7\u7a0b\u7ec4\u7684\u6240\u6709\u53c2\u6570
@operator nchar(2)=N’=’ –\u67e5\u627e\u5bf9\u8c61\u7684\u8fd0\u7b97\u7b26
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=N”float”
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 <\/p>\n
–4\u3001\u67e5\u770b\u6240\u6709\u5b58\u50a8\u8fc7\u7a0b\u5185\u5bb9
select b.name ,a.text from syscomments a,sysobjects b where object_id(b.name)=a.id and b.xtype in(‘P’,’TR’) <\/p>\n
–5\u3001\u67e5\u770b\u5305\u542b\u5b57\u7b26\u4e32\u5185\u5bb9\u7684\u5b58\u50a8\u8fc7\u7a0b <\/p>\n
select b.name ,a.text from syscomments a,sysobjects b
where
charindex(‘\u5b57\u7b26\u4e32\u5185\u5bb9’,a.text)>0 and
object_id(b.name)=a.id and b.xtype in(‘P’,’TR’) <\/p>\n","protected":false},"excerpt":{"rendered":"
–1\u3001\u67e5\u770b\u6240\u6709\u5b58\u50a8\u8fc7\u7a0b\u4e0e\u51fd\u6570 exec sp_s<\/p>\n