20 September 2007

Text search in SQL Server Procs/Views

In SQL Server 2000, sometimes one needs to find all the dependencies of one object on another. I don't trust the dependency functionality in SQL Server, so I wrote a query to scour the syscomments table in any database.

The following query allows you to search for text within queries and views in SQL Server. Just open Query Analyzer and paste the following code, replace the search string with the object name, and run.

SELECT [name]
FROM [dbo].[sysobjects] obj
INNER JOIN [dbo].[syscomments] cmt
ON obj.[id] = cmt.[id]
where cmt.[text] like '%search string%'