Search text in SQL stored procedures

Search for text in stored procedures is useful, a typical example is object renaming\remapping.

There is one example for SQL Server 2000 and two examples for SQL Server 2005 - 2008 - 2012

USE MyDatabase
GO
-----SQL Server 2000
SELECT DISTINCT so.name ,so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%myText%'
ORDER BY so.name


-----SQL Server 2005 - 2008 - 2012
GO

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition LIKE '%some_text%'
AND o.type='P'

 Related Posts

Stored Procedure Checklist

SQL Stored Procedure Commenting