3

I have a database with 100s of stored procedures, triggers and jobs. some of these use sp_send_dbmail to send out an email from within SQL SERVER, but I would like to get a list of all stored procedures/triggers/jobs that send out an email. I tried to look online for something like this, but could not find any.

How to search sql server database for string?

From the above link, I have tried a few scripts that finds all occurrences of the string(sp_send_dbmail), but that does not help me, because most of those search for data within the tables and columns, not the actual stored procedure script.

Can someone point me in the right direction.

Community
  • 1
  • 1
Phani
  • 796
  • 7
  • 21
  • 1
    Actually the link you provided has several answers that all do exactly what you are asking for. Some suggest using red gate sql search or apex which both do that. Another one has the sql needed to search through the procedure code. Look at the answer from "The Bojan". – Sean Lange Jan 19 '16 at 16:32
  • 1
    [Redgate's SQL Search](http://www.red-gate.com/products/sql-development/sql-search/) is a great tool for doing this. – Krishnraj Rana Jan 19 '16 at 16:36
  • 1
    Alternative free tool - https://www.devart.com/dbforge/sql/search/ – Devart Jan 19 '16 at 16:42
  • Thanks for your suggestion guys......Devart's answer below was good enough for me to get the list that I wanted. I would take a note of the tools mentioned by you guys too, just in case there is something similar or more complex than this I will have to do in the future. – Phani Jan 19 '16 at 17:29

1 Answers1

4
SELECT OBJECT_SCHEMA_NAME(s.[object_id]) + '.' + OBJECT_NAME(s.[object_id])
FROM sys.sql_modules s
WHERE s.[definition] LIKE '%sp_send_dbmail%'

SELECT s1.name
FROM msdb.dbo.sysjobsteps s
JOIN msdb.dbo.sysjobs s1 ON s.job_id = s1.job_id
WHERE s.subsystem = 'TSQL'
    AND s.command LIKE '%sp_send_dbmail%'
Devart
  • 119,203
  • 23
  • 166
  • 186