Use
数据库
DECLARE
@ProcName
varchar
(
50
)
Create
Table
#tmpName(Content
varchar
(
2000
))
Create
Table
#tmp(ProcName
varchar
(
2000
),Content1
varchar
(
8000
))
--
定义一个游标
DECLARE
SearchProc
CURSOR
FOR
--
查询数据库中存储过程的名称,尽量去除系统PROC,可以根据crdate时间字段来寻找非系统PROC
select
name
from
sysobjects
where
type
=
'
P
'
and
name
not
like
'
dt_%
'
OPEN
SearchProc
FETCH
NEXT
FROM
SearchProc
INTO
@ProcName
WHILE
@@FETCH_STATUS
>=
0
BEGIN
Print
@ProcName
Insert
Into
#tmpName(Content)
Exec
sp_helptext
@ProcName
Insert
Into
#tmp(ProcName,Content1)
select
@ProcName
,#tmpName.Content
from
#tmpName
--
填充完就清空一下临时表
Truncate
table
#tmpName
FETCH
NEXT
FROM
SearchProc
INTO
@ProcName
END
CLOSE
SearchProc
DEALLOCATE
SearchProc
GO
select
ProcName
from
#tmp
where
Content1
like
'
%查找内容%
'
group
by
ProcName
select
ProcName,Content1
from
#tmp
where
Content1
like
'
%查找内容%
'
select
ProcName,Content1
from
#tmp
where
procname
=
'
存储过程名称
'
--
删除临时表
Drop
Table
#tmpName
Drop
Table
#tmp