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.
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%'