ms sql server 搜索存儲過程語句中的字符串

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

 

此条目发表在Program, Windows分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>