問題描述
我需要計算所有存儲過程中特定單詞的出現次數.
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
這篇關于SQL- 計算所有存儲過程中特定單詞的出現次數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！