Search Text in Database’s Objects

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’

*/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: