How to search a specific text in a SQL Server Stored Procedures, Functions, Views or Triggers

As a DB developer, I often needed to find a stored procedures, functions, views and triggers  that contains a specific piece of text string. For an example, say I want to find any Stored Procedures, Views or Functions that contains a text ‘codemog’.

So, How to search a specific text in a SQL Server Stored Procedures,Views or functions etc ?
SQL Server has in-built system views that keeps the T-SQL code of the Database Objects, like Functions, Stored Procedures, Views, Triggers, etc.

1. Using SQL_MODULES

We can do this using SQL_MODULES system view. See the below code:

1
2
3
4
5
6
7
 SELECT DISTINCT 
      A.NAME AS OBJECT_NAME,
      A.TYPE_DESC
      FROM SYS.SQL_MODULES M 
      INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID
      WHERE M.DEFINITION LIKE '%codemog%'
      ORDER BY TYPE_DESC

definition column of sys.sql_modules view contains actual T-SQL code stored in a view, table valued or scalar function, stored procedure, trigger.

2. Using SYSCOMMENTS

We can also do this using SYSCOMMENTS system view. See the below code:

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @textSearch NVARCHAR(255)='codemog'
 
SELECT DISTINCT sysobjects.name AS [Object Name] ,
       CASE WHEN sysobjects.xtype = 'P' THEN 'STORED PRECEDURE'
            WHEN sysobjects.xtype = 'TF' THEN 'FUNCTION'
            WHEN sysobjects.xtype = 'TR' THEN 'TRIGGER'
            WHEN sysobjects.xtype = 'V' THEN 'VIEW'
       END AS [Object TYPE]
FROM sysobjects,syscomments
     WHERE sysobjects.id = syscomments.id
       AND sysobjects.type IN ('P','TF','TR','V')
       AND sysobjects.category = 0
       AND CHARINDEX(@textSearch ,syscomments.text)>0

syscomments contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the actual T-SQL statements.

NOTE:
As per microsoft, This feature will be removed in a future version of Microsoft SQL Server. We recommend that you use sys.sql_modules instead.

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.