SQL- 计算所有存储过程中特定单词的出现次数

问题描述

我需要计算所有存储过程中特定单词的出现次数.

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!

推荐答案

我会使用 object_definition 函数 和 sys.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+'%'

这是工作(并在评论后更新)示例:http://sqlfiddle.com/#!3/a759c/7

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