Finding a Needle in a Haystack

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!

    I'm interested in...


    Related News