SQL- Count occurrences of a specific word within all stored procedures

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?

  • SQL Server Source Control
  • how to update using nested query in SQL
  • SQL - Select all when filter value is empty
  • Trying to escape a sp_executesql variable
  • I can not save my user defined function in my Database in sql server 2012
  • SQL Different column type comparison error
  • I was trying to do this using cursors, but I am not getting anywhere!

    One Solution collect form web for “SQL- Count occurrences of a specific word within all stored procedures”

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.