Search Text in Database’s Objects

July 14, 2009

Here is the SQL Stored Procedure which will list all the database’s Objects(SP, Views, Functions etc…) in which the Searched Text exist.

CREATE PROCEDURE [dbo].[SearchTextInDatabase]
@SearchText VARCHAR(200)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX)

SET @SQL = ‘SELECT Name FROM SysObjects WHERE ID IN
(SELECT ID FROM SysComments WHERE Text Like ”%’ +   @SearchText + ‘%”)
ORDER By Name’

EXEC(@SQL)
END

/*
Here is the Example How to use it….. 🙂

EXEC [SearchTextInDatabase] ‘SearchText’

*/