This procedure will allow you to find any string in any programmable DB Object with its name or part of its name.
Search for a Value Throughout Your Database
Learn how to search for a value throughout all database tables.
2013-03-14
7,230 reads
/* 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