Anatoly Lubarsky Logo
programming, design, integration, games, music

Stored Procedures Code Search

Suppose you have 1000 stored procedures in the database. Now you have to find something out there in the code. Suppose you want to drop the redundant database table and don't know about whether some procedure uses it. This is a small procedure I build, that would help you to perform such search:


create proc P_TEXT_SEARCH_SPS
(
   @p_word varchar(30)
)
as
begin
   declare proccurs cursor
   for
      select C.[text], O.[name]
      from 
         sysobjects O join syscomments C on
         C.[id] = O.[id]  
      where O.xtype = 'p' and O.status > 0
   open proccurs
   declare @ptext varchar(2000)
   declare @pname char(40)
   fetch next from proccurs into @ptext, @pname
   while @@fetch_status = 0
   begin
      print @pname + ' = ' + 
         convert(varchar(5), charindex(@p_word, @ptext, 0))
      fetch next from proccurs into @ptext, @pname
   end
   close proccurs
   deallocate proccurs
end 

That's it. Happy searching :)


Related Posts:

Tuesday, June 22, 2004 2:47 AM

Comments

# re: Stored Procedures Code Search
Thanks, this was helpful!

So I improved on it!

It's a table-valued UDF and searches lots of other stuff. Also returns almost the whole source code (show results in Grid):

CREATE function dbo.fn_util_SearchSprocs (@p_word varchar(30))
returns @SearchResults table
(
Type char(2),
[Name] sysname,
[Offset] varchar(5),
[Text] nvarchar(3000)
)
as
begin
insert into @SearchResults
select so.xtype, so.[name], convert(varchar(5), charindex(@p_word, sc.[text], 0)), cast(sc.[text] as nvarchar(3000))
from sysobjects as so
inner join syscomments as sc
on sc.[id] = so.[id]
--sprocs, triggers, UDFs, table-valued UDFs, views, defaults, and check-constraints
where so.xtype IN ('P', 'TR', 'FN', 'TF', 'V', 'D', 'C')
and so.status > 0
and charindex(@p_word, sc.[text], 0) > 0

return
end

7/17/2004 2:03 AM by Dave

# re: Stored Procedures Code Search
Forgot to mention this for newbies:

To try this out, run this:

select *
from dbo.fn_util_SearchSprocs('dave')

That will find all the code with your name in it. If your name is 'dave', that is :)

Dave

7/17/2004 2:05 AM by Dave

# re: Query for a list of Procs that contain a string

7/23/2004 9:14 PM by Jeff Papiez

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs