Tag: TSQL

How to find all jobs that call a stored procedure.

I need to find all SQL Agent jobs that call a certain stored procedure. You could open every Agent job and inspect each step or you could do it the smart way and use a query! You can also use this method to find other items (tables, views, functions, etc.)

Read more: How to find all jobs that call a stored procedure.

I need to find any job that is using a stored procedure. Using this query below, I just need to modify the WHERE clause for what I am searching for. Also make sure you change the database to MSDB.

use msdb
GO
SELECT Job.name AS JobName,
Job.enabled AS ActiveStatus,
JobStep.step_name AS JobStepName,
JobStep.command AS JobCommand
FROM sysjobs Job
INNER JOIN sysjobsteps JobStep
ON Job.job_id = JobStep.job_id
WHERE JobStep.command LIKE '%spBHTamper%'

The results show two different Jobs that have spBHTamper in it.

I open up the Tamper Export job, and I see that indeed the spBHTTamperExport is being called. Success!

To read more about the MSDB database, please visit this link: https://learn.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-ver16

TSQL – Easy way to find rows that do not exist in another table using the EXCEPT operator

There are times you have two tables that look the same and seem to have the same rows in each. Using the EXCEPT operator, you can find any rows that do not exist in either table.

Continue reading TSQL – Easy way to find rows that do not exist in another table using the EXCEPT operator

How to find information about your instance using SERVERPROPERTY.

Here is a way to find out information about your SQL Server instance using TSQL.  You can query  all kinds of properties, for example, what edition are you running, what server is it on, etc.

Continue reading How to find information about your instance using SERVERPROPERTY.