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)

Share

Share

Rate

3.8 (5)