2

I need to count the occurrences of a particular word in all of my stored procedures.

i.e. How many times does the word 'place' occur in all of the stored procedures within a specific database?

I was trying to do this using cursors, but I am not getting anywhere!

Leigh
  • 28,765
  • 10
  • 55
  • 103
Rk1
  • 21
  • 2
  • please at some of the code you used, then we can get a better idea of what you are trying to do. – mariomario May 25 '12 at 05:50
  • Do you have the .sql files for the stored procs? You could just do string matching on the content.... – TGH May 25 '12 at 05:52
  • 1
    SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%place%' AND ROUTINE_TYPE='PROCEDURE' – Rk1 May 25 '12 at 05:53
  • This query gave me the list of sp containing word place...now how do i count the occurences of 'place' in the sp definitions...? – Rk1 May 25 '12 at 05:54
  • Use [SYS.SQL_MODULES](http://stackoverflow.com/questions/1506082/find-all-references-to-view) – OMG Ponies May 25 '12 at 05:56
  • using sys.sql_modules returns me user-defined functions too...i want only stored procedures – Rk1 May 25 '12 at 06:14
  • Why you want to do this in DB. Just curious :-) – ejb_guy May 25 '12 at 06:18

1 Answers1

5

I would use object_definition function and sys.procedures view this way:

declare @word varchar(128)
set @word = 'place'

select name, (len(object_definition(object_id)) -  len(replace(object_definition(object_id), @word, ''))) / len (@word) as qty
from sys.procedures
where object_definition(object_id) like '%'+@word+'%' and type = 'P'
order by name

Added after comment, all occurrences of specific word in all stored procedures:

declare @word varchar(128)
set @word = 'place'

select sum((len(object_definition(object_id)) -  len(replace(object_definition(object_id), @word, ''))) / len (@word)) as qty
from sys.procedures
where object_definition(object_id) like '%'+@word+'%'

Here is working (and updated after comment) example: http://sqlfiddle.com/#!3/a759c/7

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • Thanks a lot...could u also suggest how to get the total of this..in your example it lists procedure name and qty...what if we want to display only sum of qty? i.e. total occurences of word place? – Rk1 May 25 '12 at 06:53