You can execute the following content in the master database in all databases and search the Stored Procedure content by keywords,
The SP _ before the name sp_findproc seems to be required. Otherwise, if it is executed in other databases, the system will prompt that it cannot be found.
Create Procedure [ DBO ] . [ Sp_findproc ] ( @ Akey Varchar ( 255 )) As
-- Search for keywords in all stored procedures. keywords are case insensitive.
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 @ Plain text As Varchar ( 255 )
Create Table # Temp_proctext (OID Int Identity ( 1 , 1 ), Name Varchar ( 700 ), 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 ' % ' + @ Akey + ' % ' Order By A. Name, B. colid
Open Proctext_cursor
Fetch Next From Proctext_cursor Into @ 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 @ Plain text = Substring ( @ Atext , @ Start , @ End - @ Start )
Insert Into # Temp_proctext Values ( @ Aname , @ Plain text ,( @ Acolid - 1 ) * 4000 + @ POS )
Set @ POS = Charindex ( @ Akey , @ Atext , @ End )
End
Fetch Next From Proctext_cursor Into @ Aname , @ Atext , @ Acolid
End
Close Proctext_cursor
Deallocate Proctext_cursor
Select Name, POs, Text From # Temp_proctext Order By Oid
Drop Table # Temp_proctext
Go
