利用SQL语句查找某数据库中所有存储过程包含的内容

2018-06-24

SQL

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

Related Posts