Technical Article

Simplest way to search any string in db objects

,

This procedure will allow you to find any string in any programmable DB Object with its name or part of its name.
/*
	Name: p_Find_DbObjects
	Parameters: 
	 @string - String/Part of string for DB Object you want to search.
	Description: This procedure will allow you to find any string in any DB Object with its name or part of its name.
*/
CREATE PROCEDURE [dbo].[p_Find_DbObjects]
 @string NVARCHAR(MAX) = ''
AS
BEGIN
	
	SET NOCOUNT ON
	SET ANSI_WARNINGS OFF
	
	-- Returns list of DB objects containing the string in their definition and/or in their name.
	SELECT [DB_ObjName] = OBJECT_SCHEMA_NAME(sm.[object_id]) + '.' + OBJECT_NAME(sm.[object_id]), so.type_desc [DB_ObjType], 
	 (LEN(sm.[definition]) - LEN(REPLACE(sm.[definition], CHAR(10), ''))) Lines_of_Code
	FROM SYS.SQL_MODULES sm
	 INNER JOIN SYS.OBJECTS so
		ON sm.[OBJECT_ID] = so.[OBJECT_ID]
	WHERE sm.[definition] LIKE N'%' + @string + '%'
	 OR so.[name] like N'%' + @string + '%'
	GROUP BY sm.[object_id], so.type_desc,sm.[definition]
	ORDER BY [DB_ObjName], [DB_ObjType];
	
	SET NOCOUNT OFF
	SET ANSI_WARNINGS ON
	
END

Rate

3.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.8 (5)

You rated this post out of 5. Change rating