Use [EShop]
GO
/****** object:storedprocedure [dbo]. [Sp_findproc] Script DATE:2015/8/19 11:05:24 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo]. [Sp_findproc] (@akey varchar (255)) As
--Find keywords in all stored procedures, keywords are not case sensitive
DECLARE @aname as varchar (700)
DECLARE @atext as varchar (8000)
DECLARE @acolid as int
DECLARE @pos as int
DECLARE @start as int
DECLARE @end as int
DECLARE @shorttext as varchar (255)
CREATE TABLE #temp_proctext (OID int IDENTITY (1, 1), name varchar (), text varchar (255), POS int)
Declare proctext_cursor cursor Local for
Select A.name, b.text,b.colid from sysobjects a,syscomments B where a.id=b.id and a.type= ' P '
And b.text like '% ' [e-mail protected]+ '% ' ORDER by a.name,b.colid
Open Proctext_cursor
FETCH NEXT from Proctext_cursor to @aname, @atext, @acolid
while (@ @FETCH_STATUS = 0)
Begin
Set @pos = charindex (@akey, @atext, 1)
while (@pos >0)
Begin
Set @start = @pos-10
If @start <=0
Set @start =1
Set @end = @pos + len (@akey) +20
If @end > Len (@atext)
Set @end = Len (@atext)
Set @shorttext = Substring (@atext, @start, @end-@start)
INSERT into #temp_proctext values (@aname, @shorttext, (@acolid-1) *[email protected])
Set @pos = charindex (@akey, @atext, @end)
End
FETCH NEXT from Proctext_cursor to @aname, @atext, @acolid
End
CLOSE Proctext_cursor
Deallocate proctext_cursor
Select Name,pos,text from #temp_proctext ORDER by OID
drop table #temp_proctext
"SQL statement"-find keywords in all stored procedures, keywords are not case-sensitive [Sp_findproc]