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.
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.