How to search all objects in Microsoft SQL Server

I use this piece of SQL often. Just comment out the parts in the “WHERE” clause you don’t need.

SELECT DISTINCT
       o.name,
       o.type_desc,
	   m.definition
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE 
 1=1
 and m.definition Like '%test%'  /*find whats in the body of the object*/ 
 and o.name like '%insert%' /*searches the object name. */
 and o.type_desc in ('SQL_STORED_PROCEDURE') /*SYSTEM_TABLE,VIEW,SQL_STORED_PROCEDURE,FOREIGN_KEY_CONSTRAINT,SERVICE_QUEUE,USER_TABLE,PRIMARY_KEY_CONSTRAINT, INTERNAL_TABLE*/

You will see three different filters, one for definition, name and type_desc. If you run into any authorization errors, please talk to your database admin (DBA)

• definition: This is the one I use most often. It searches through all object body (stored proc logic, etc) and displays the result. Try and add a type_desc filter to speed things along.
• name: This is the actual object name (example. Sotred procedure name, table name, etc)
• type_desc: What type of object it is. I added all the different types in the code comment.