{"id":137,"date":"2015-11-16T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/t-sql-search-snippets"},"modified":"2026-03-18T21:52:16","modified_gmt":"2026-03-18T21:52:16","slug":"t-sql-search-snippets","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/11\/16\/t-sql-search-snippets\/","title":{"rendered":"T-SQL Search Snippets – SQL Hammer"},"content":{"rendered":"

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.<\/p>\n

Object search<\/h2>\n
USE [$databaseName$]\nDECLARE @keyword VARCHAR(128) = '$keyword$'\n\n
SELECT o.[type_desc]\n, s.name [schema]\n, o.name [table]\n, c.name [column]\nFROM sys.objects o\nINNER JOIN sys.schemas s ON s.schema_id = o.schema_id\nLEFT JOIN sys.columns c ON c.object_id = o.object_id\nWHERE o.name LIKE '%' + @keyword + '%'\nOR c.name LIKE '%' + @keyword + '%'\nOR s.name LIKE '%' + @keyword + '%'\nORDER BY o.[type_desc], s.name, o.name, c.name<\/code><\/pre>\n<\/p>\n
SQL Agent job search<\/h2>\n
DECLARE @keyword VARCHAR(128) = '$keyword$'\n\n
SELECT j.name\n,js.step_name\n,js.command\nFROM msdb.dbo.sysjobs j\nINNER JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id\nWHERE j.name LIKE '%' + @keyword + '%'\nOR js.step_name LIKE '%' + @keyword + '%'\nOR js.command LIKE '%' + @keyword + '%'\nORDER BY j.name, js.step_name, js.step_id<\/code><\/pre>\n<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and entrepreneurial ventures.<\/p>\n
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<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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] , […]<\/p>\n","protected":false},"author":1,"featured_media":530,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=137"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":399,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions\/399"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/530"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}