STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Guy Glantser

How to Find Objects that Contain a Certain String in SQL Server

Let’s say you remember a piece of code you need to optimize or take a look at, but you don’t remember in which stored procedure you saw it.


The following script searches for a certain string inside the code of all programmatic objects in the current database (stored procedures, triggers, views, and user-defined functions), and returns the objects which contain it.


SELECT

SchemaName = SCHEMA_NAME ([Objects].schema_id) ,

ObjectName = [Objects].[name] ,

ObjectType = [Objects].[type_desc] ,

[Definition] = SQLModules.[definition]

FROM

sys.sql_modules AS SQLModules

INNER JOIN

sys.objects AS [Objects]

ON

SQLModules.[object_id] = [Objects].[object_id]

WHERE

SQLModules.[definition] LIKE N'%Your String Here%';

JOIN OUR MAILING LIST

CONTACT US

4 Itzhak Ben Zvi, Hod-Ha'sharon,

Israel 4537302

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle