1. Create a procedure
-----code:start------------------------------------------------------------
CREATE PROCEDURE [dbo].[Test_DBA_FindStringInDB]
(
/* String to find */
@chvStringToFind varchar(256)
/* Only look for objects of this type */
,@chrObjectType char(2)=null
/* Number of characters to extract before and after the string to find */
,@intNbCharToExtract int=50
)
AS
SET NOCOUNT ON
BEGIN
SELECT
t.Name
,t.TypeDescription
,t.CreationDate
,t.ModificationDate
,'...' + SUBSTRING
(
t.ObjectDefinition,
CHARINDEX
(@chvStringToFind, t.ObjectDefinition) - @intNbCharToExtract,
LEN(@chvStringToFind) + (@intNbCharToExtract*2)
) + '...' AS Extract
FROM
(
SELECT
o.name AS Name
,o.type_desc AS TypeDescription
,o.create_date AS CreationDate
,o.modify_date AS ModificationDate
,OBJECT_DEFINITION(object_id) AS ObjectDefinition
FROM sys.objects o
WHERE
(
(
o.type IN ('AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X')
AND @chrObjectType IS NULL
)
OR o.type = @chrObjectType
)
AND OBJECT_DEFINITION(o.object_id) LIKE '%' + @chvStringToFind + '%'
) AS t
ORDER BY TypeDescription, Name
END
-----code:end------------------------------------------------------------
2. Try to run the procedure
-----code:start-----------------------------------------------------------
exec Test_DBA_FindStringInDB
@chvStringToFind='BANK'
,@chrObjectType=null
,@intNbCharToExtract=50
-----code:end------------------------------------------------------------
3. Example Results Messages
4. Optional: To remove procedure
-----code:start-----------------------------------------------------------
drop procedure [dbo].[Test_DBA_FindStringInDB]
-----code:end------------------------------------------------------------
Source:
http://www.sqlservercentral.com/scripts/String+Match/70371/
Article: Find a string in database objects