查找数据库中所有存储过程的内容

最新推荐文章于 2024-04-21 19:34:31 发布

mmcgzs

最新推荐文章于 2024-04-21 19:34:31 发布

阅读量1.3k

点赞数

文章标签：数据库存储 table insert

Use gim
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 '%GetQuestionClassNameByCode%' group by ProcName

select ProcName,Content1 from #tmp where Content1 like '%GetQuestionClassNameByCode%'

select ProcName,Content1 from #tmp where procname='存储过程名称'
--删除临时表
Drop Table #tmpName
Drop Table #tmp