T-SQL Search Snippets – SQL Hammer

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

Written by Derik Hammer of SQL Hammer

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and entrepreneurial ventures.

Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com as one small way to give back and continue the cycle of shared learning.

Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource.

For more information, visit http://www.sqlhammer.com. Follow Derik on Twitter for SQL tips and chat