refer from: http://yixin416757625.iteye.com/blog/1472899
可以方便的在所有的存儲過程腳本中查找字符串:
USE [test]
GO
/****** 对象: StoredProcedure [dbo].[search_proc_word] 脚本日期: 08/07/2012 09:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: <Author,,Name>
– Create date: <Create Date,,>
– Description: <Description,,>
– =============================================
CREATE PROCEDURE [dbo].[search_proc_word]
– Add the parameters for the stored procedure here
@word varchar(100)
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
Declare @proc_name varchar(100)
Declare @fuction_name varchar(100)
Declare @seek_text varchar(100)
set @fuction_name= @word –要查找的字符串
set @[email protected]_name
Declare @sql varchar(8000)
Create table #proc_count(proc_name varchar(100),fuction_name varchar(100),[text] varchar(7000))
Create table #proc_text([text] varchar(8000))
Declare Cursor_Function cursor
for
select quotename(name) from sys.sysobjects where xtype=’P’
Open Cursor_Function
Fetch next from Cursor_Function into @proc_name
While @@fetch_status=0
Begin
truncate table #proc_text
set @sql=”
set @[email protected]+’
insert into #proc_text([text])
exec sp_helptext ‘+””[email protected]_name+””+’
If exists(select 1 from #proc_text where [Text] like ‘+””+’%’[email protected]_text+’%’+””+’)
begin
insert into #proc_count(proc_name,fuction_name,[text])
select ‘+””[email protected]_name+””+’,’+””[email protected]_name+””+’,’+’[text]
from #proc_text
where [Text] like ‘+””+’%’[email protected]_text+’%’+””+’
end’
print @sql
exec (@sql)
Fetch next from Cursor_Function into @proc_name
end
close Cursor_Function
deallocate Cursor_Function
select distinct proc_name,fuction_name from #proc_count
order by proc_name
drop table #proc_count
drop table #proc_text
END