To me, my SQL Saturdays posts aren’t just a method of information sharing or a way to teach new SQL Server database administrators. I also use it as a repository for my own purposes, recording bits of code or self-taught database lessons I want to remember for future work. Today’s post is T-SQL that I use for searching object definitions. I don’t use it nearly often enough for my fingertips to remember how to type from scratch each time, but I use it just often enough that I need to have it archived. And if it helps out anyone who reads this blog, then that’s just gravy, right?
There are three different queries I use. One for searching the text of views, one for searching the text of functions and stored procedures, and one for searching the text of job steps. They are listed in that order. Of course, the word MySearchTerm needs to be replaced with whatever phrasing the DBA is looking for.
USE MyDB;
GO
SELECT v.Name, m.Definition
FROM sys.views v
INNER JOIN sys.sql_modules m
ON v.Object_ID = m.Object_ID
WHERE m.Definition LIKE ‘%MySeachTerm%’
ORDER BY v.Name;
–Search view definitions
SELECT so.Name, so.type, so.schema_id, su.Name, sm.definition
FROM sys.objects so
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ‘%MySearchTerm%’
–AND so.schema_id <> 1 /*schema_id looks for non-dbo schema objects*/
ORDER BY so.type, so.Name
–Search functions and stored procedures
USE MSDB
GO
SELECT sj.name, sjs.*
FROM sysjobsteps sjs
INNER JOIN sysjobs sj
ON sjs.job_id = sj.job_id
WHERE command LIKE ‘%MySeachTerm%’;
–Search job steps
Does this solve a particular need you might have? Are there other day-to-day queries you’d like to see me talk about in SQL Saturdays?
Let me know.
