Earlier today we encountered an issue where one SQL table in a data warehouse was not being updated. We were tasked with finding the one line of code out of tens of thousands of lines of code that might be updating this table to troubleshoot.
I wanted to share the SQL scripts that I used to find where the table was being referenced. There are two:
The first script searches the text of all SQL server stored procedures for a specific word or phrase. If you run this, make sure to keep the “%” symbols. Run this against a specific database.
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like ‘%ENTER YOUR TEXT HERE%’;
The second script searches all job steps for a specific word or phrase. Run this against the MSDB database:
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 ‘%ENTER YOUR TEXT HEREt%’
These scripts make an otherwise tedious job quite fast. Please contact Opal Business Solutions, a Microsoft Gold Partner, for more handy SQL scripts. Try it out!
