Search All Database Objects for a string

,

Lets you search all db objects for a string.

EXECUTE spFindTextInObjects 'UNION ALL', 1
GO
EXECUTE spFindTextInObjects 'XML EXPLICIT'
GO
EXECUTE spFindTextInObjects 'DECLARE%CURSOR', 1
GO
EXECUTE spFindTextInObjects '@@IDENTITY', 1
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFindTextInObjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFindTextInObjects]
GO

CREATE   PROCEDURE spFindTextInObjects (@findText nvarchar(1000),
	@havingCountGreaterThan smallint = 0)
AS
-- 	Example Calls
-- 	EXECUTE spFindTextInObjects 'UNION ALL', 1 
-- 	EXECUTE spFindTextInObjects 'XML EXPLICIT'
-- 	EXECUTE spFindTextInObjects 'DECLARE%CURSOR', 1
-- 	EXECUTE spFindTextInObjects '@@IDENTITY', 1
-- 	GO
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @findText2 nvarchar(1002)

	IF @findText IS NULL 
		RETURN

	SET @findText2 = '%' + UPPER(@findText) + '%'

	SELECT COUNT(*) As [FindCount], 
		OBJECT_NAME([id]) As [Name],
		[id]
	FROM syscomments  
	WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
		AND PATINDEX(@findText2, UPPER([text])) > 0
	GROUP BY [id]
	HAVING (COUNT(*) > @havingCountGreaterThan)
	ORDER BY [FindCount] DESC, 
		[Name]
		
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	RETURN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate