SQL Server, find text in a Trigger, Stored Procedures, View and Function. Two ways and what ways is better

Hi guys,

Welcome back!

Today a light post about a question that has been asked to me several times.

The question is: how to search for text inside a trigger or a stored procedure?

We have two different ways.

A first way using a system view called syscomments:

SELECT
  obj.xtype,text, *
FROM sysobjects obj
  JOIN syscomments com on obj.id = com.id
WHERE
    TEXT like '%text_that_i_am_searching_for%'

A second way using a system view called sql_module:

       
SELECT
  o.name AS Object_Name,
  o.type_desc,
  m.definition
FROM sys.sql_modules m
  JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%text_that_i_am_searching_for%';     
 

 

Another question is?

What differences do we have in using one way instead of the other?

Which solution is better?

You have to consider that the system view sys.syscomment is obsolete and will be removed sooner or later. So don't use it in production enviroiments.

The sys.sql_modules system view was created to replace the sys.syscomment system view.

The main advantage of using the view sql_module are:

• it returns UDF (user defined functions) information.
• the text field that stores the object definition is no longer limited to 4000 characters.

So, for the above reasons, I prefer to use sql_modules over syscomments.

That's all for today!

I hope you liked this post and found it useful.

Luca

SQL: READY TO RUN FASTER?

Previous post: SQL Server Monitoring. A look at Redgate's SQL Monitor