Searching text in SQL database objects - Stored Procedures (SP), Views, Triggers, Tables-

Here is list of all SQL Objects
:

For reference I have used ‘AdventureWroks’ database and here I have created Stored procedure to give me all SQL objects using search text.

you can also apply filter based on

 

USE AdventureWorks

GO

 ALTER PROCEDURE [dbo].[FindTextInDatabaseObjects] @SearchText VARCHAR(255)

AS

  BEGIN

      SELECT DISTINCT o.name  AS Object_Name,

                      o.type_desc as Object_Type,

                      @SearchText SearchCriteria

      FROM   sys.sql_modules m

             INNER JOIN sys.objects o

                     ON m.object_id = o.object_id

      WHERE  m.definition LIKE '%' + @SearchText + '%'

      ORDER  BY 2,1            

  END

GO

EXEC [Findtextindatabaseobjects] 'customer'

-- output will be

Object_Name

Object_Type

SearchCriteria

ufnGetContactInformation

SQL_TABLE_VALUED_FUNCTION

customer

iduSalesOrderDetail

SQL_TRIGGER

customer

vIndividualCustomer

VIEW

customer

 

If you want you can filter output based on object type -stored procedure or views

 e.g.    WHERE o.type_desc in ('SQL_STORED_PROCEDURE', 'VIEW')