T-SQL Search Snippets

Here are two snippets that I use often to search for objects on a variety of servers. The first will search any object within a database and the second will search various elements of SQL Agent jobs.

Object search

USE [$databaseName$]

DECLARE @keyword VARCHAR(128) = '$keyword$'

SELECT o.[type_desc]
, s.name [schema]
, o.name [table]
, c.name [column]
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
LEFT JOIN sys.columns c ON c.object_id = o.object_id
WHERE o.name LIKE '%' + @keyword + '%'
OR c.name LIKE '%' + @keyword + '%'
OR s.name LIKE '%' + @keyword + '%'
ORDER BY o.[type_desc], s.name, o.name, c.name

SQL Agent job search

DECLARE @keyword VARCHAR(128) = '$keyword$'

SELECT j.name
,js.step_name
,js.command
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
WHERE j.name LIKE '%' + @keyword + '%'
OR js.step_name LIKE '%' + @keyword + '%'
OR js.command LIKE '%' + @keyword + '%'
ORDER BY j.name, js.step_name, js.step_id

This article has 1 comment

  1. […] Derik Hammer shares a couple of snippets¬†he uses to find objects and SQL Agent jobs. […]

Leave a Reply