查找存储过程包含内容
Use数据库
DECLARE@ProcNamevarchar(50)
CreateTable#tmpName(Contentvarchar(2000))
CreateTable#tmp(ProcNamevarchar(2000),Content1varchar(8000))
--定义一个游标
DECLARESearchProcCURSORFOR
--查询数据库中存储过程的名称,尽量去除系统PROC,可以根据crdate时间字段来寻找非系统PROC
selectnamefromsysobjectswheretype='P'andnamenotlike'dt_%'
OPENSearchProc
FETCHNEXTFROMSearchProc
INTO@ProcName
WHILE@@FETCH_STATUS>=0
BEGIN
Print@ProcName
InsertInto#tmpName(Content)Execsp_helptext@ProcName
InsertInto#tmp(ProcName,Content1)select@ProcName,#tmpName.Contentfrom#tmpName
--填充完就清空一下临时表
Truncatetable#tmpName
FETCHNEXTFROMSearchProc
INTO@ProcName
END
CLOSESearchProc
DEALLOCATESearchProc
GO
selectProcNamefrom#tmpwhereContent1like'%查找内容%'groupbyProcName
selectProcName,Content1from#tmpwhereContent1like'%查找内容%'
selectProcName,Content1from#tmpwhereprocname='存储过程名称'
--删除临时表
DropTable#tmpName
DropTable#tmp