检查当前库下内容含有指定字符的所有存储过程

由于配置表改变了，要对当前数据库下所有SP进行检查，对于有引用这个表的SP都要作相应的改变，如果是一个一个SP查询，那工作量太大了，所以写了一个SP，对包含有对该表引用的SP都列出来。

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:jim.yang
-- Create date: 2009-9-2
-- Description:检查当前库下内容含有指定字符的所有存储过程
-- #p_procedure_searchStr 'sms_5'
-- =============================================
ALTER PROCEDURE #p_procedure_searchStr
    @str varchar(100)
AS
BEGIN
    create table #t1(name varchar(100),text varchar(8000))
    create table #t2(text varchar(8000))
    
    --得到库所有的sp
    select  identity(int,1,1) autoid,name into #proc 
    from sys.Procedures
    where type='P'
    

    declare @autoid int
    set @autoid=1
    declare @spName varchar(1000)

    --循环得到sp的内容文本
    declare @sql nvarchar(4000)
    while @autoid>0 begin
        
        truncate table #t2
        
        select @spName=name from #proc where autoid=@autoid
        set  @sql='sp_helpText ['+@spName+']'
        
        insert into #t2(text)
        execute(@sql)
        
        insert into #t1(name,text)
        select @spName,text from #t2
        where text like '%'+@str+'%'
        
        
        set @autoid=@autoid+1
        
        if not exists(select * from #proc where autoid=@autoid) 
            set @autoid=0
            
    end
    
    select * from #t1
    
    drop table #t1,#t2,#proc
END
GO