Search in your DB-Objects With Multiple Keywords

Introduction

I had a problem with my database, to find certain procedures in my database, I found SQL code to find Objects containing one string, but it wasn't exactly what I wanted, so I developed this SQL code.

Background

I've developed a procedure that takes multiple keywords, and the @MinCount of matched keywords in an object to return.

Using the Code

Don't forget to change [YOUR_DATABASE] to your target database.

Here is the body of the procedure, you have to execute the code first in your DB, or just take the body and run it from a file if you want.

USE [YOUR_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[SearchSPs]    Script Date: 08/26/2013 19:25:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Ahmad Magdy
-- Create date: 8-17-2013 16:56
-- Description:	Search in SPs
-- =============================================
CREATE PROCEDURE [dbo].[SearchSPs] 
	@SearchWords NVARCHAR(250) = NULL, 
	@Separator VARCHAR(50) = '-',
	@MinCount INT = NULL
AS
BEGIN
SET NOCOUNT ON;
IF(@SearchWords IS NULL)
BEGIN
	RETURN
END
IF(@Separator IS NULL)
BEGIN
	SET @Separator = '-'
END
DECLARE @Strings TABLE (String NVARCHAR(250))
DECLARE @OriginalStrings TABLE (String NVARCHAR(250))
DECLARE @Results TABLE (ID INT, Name NVARCHAR(100), _
Type NVARCHAR(2), Words NVARCHAR(MAX), Text NVARCHAR(MAX))
SET @SearchWords = ' SELECT ''' + _
REPLACE(@SearchWords, @Separator, ''' UNION ALL SELECT ''') + ''' '
INSERT INTO @Strings EXEC(@SearchWords)
INSERT INTO @OriginalStrings SELECT * FROM @Strings
UPDATE S SET S.String = '%' + S.String + '%' FROM @Strings S
 
IF(@MinCount IS NULL) BEGIN SET @MinCount = _
(SELECT COUNT(String) FROM @Strings) END
DECLARE @ID INT, @Name NVARCHAR(100), @Type NVARCHAR(2), _
@ObjectsCursorWords NVARCHAR(MAX), @Text NVARCHAR(MAX)
DECLARE ObjectsCursor Cursor FOR 
SELECT	SO.ID AS [OBJECT_ID],
		SO.Name AS [OBJECT_NAME],
		SO.[Type] AS [OBJECT_TYPE],
		(SELECT SC.Text FROM SYSCOMMENTS SC WHERE SC.ID = SO.ID) AS [OBJECT_TEXT]
FROM SYSOBJECTS SO (NOLOCK)
WHERE	[NAME] NOT LIKE '%DNU%'
AND  (SO.Type = 'P' OR SO.Type = 'TF' /* Check Link : _
http://technet.microsoft.com/en-us/library/ms190324.aspx */)
AND	 (SELECT COUNT(String) FROM @Strings WHERE SO.name LIKE String) >= @MinCount
OR	EXISTS(
		SELECT	* 
		FROM	SYSCOMMENTS SC (NOLOCK) 
		WHERE	SO.ID = SC.ID AND (SELECT COUNT(String) _
		FROM @Strings WHERE SC.Text LIKE String) >= @MinCount
		)
ORDER BY SO.Type, SO.Name
OPEN ObjectsCursor
FETCH NEXT FROM ObjectsCursor INTO
@ID, @Name, @Type, @Text
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @ObjectsCursorWords = ''
	
	SELECT @ObjectsCursorWords = _
	COALESCE(@ObjectsCursorWords + ' ' + OS.String, @ObjectsCursorWords)
	FROM SYSOBJECTS SO
	LEFT JOIN SYSCOMMENTS SC ON SO.ID = SC.ID 
	LEFT JOIN @Strings S ON S.String = S.String
	LEFT JOIN @OriginalStrings OS ON OS.String LIKE S.String
	WHERE SO.ID = @ID AND SC.TEXT LIKE S.String _
	AND @ObjectsCursorWords NOT LIKE S.String 
	INSERT INTO @Results
	SELECT @ID, @Name, @Type, @ObjectsCursorWords, @Text
	
	FETCH NEXT FROM ObjectsCursor INTO
	@ID, @Name, @Type, @Text
END
CLOSE ObjectsCursor
DEALLOCATE ObjectsCursor
SELECT R.Name, R.Type, R.Words, R.Text
FROM @Results R
DELETE @Strings
DELETE @OriginalStrings
DELETE @Results
END 

You have to send the keywords in a string, and send the separator between these keywords, and the min count of found items to consider this Objects a hit.

USE [YOUR_DATABASE] 
GO 
DECLARE	@return_value int
EXEC	@return_value = [dbo].[SearchSPs]
		@SearchWords = N'Withdraw Orders Sales Addition Stores Transfer',
		@Separator = N' ',
		@MinCount = 1
SELECT	'Return Value' = @return_value
GO   

You can set the Type of returned Objects. Here, I return 'P' StoredProcedure and 'TF' Functions, you can extend this to find 'U' Tables-UserDefined.

 AND  (SO.Type = 'P' OR SO.Type = 'TF' /* Check Link : _
http://technet.microsoft.com/en-us/library/ms190324.aspx */) 

You can also use UNION instead of UNION ALL to get DISTINCT keywords. 

' SELECT ''' + REPLACE(@SearchWords, @Separator, ''' UNION ALL SELECT ''') + ''' ' 

Points of Interest

What is really helpful for me is that it returns the matched keywords for each object.