SQL Script to find SQL Objects containing specific text

I did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I’ve previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we’d identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SearchString VARCHAR(100) = 'string to find'

SELECT DISTINCT
	['sys objects'].name AS 'Object Name'
	,['sys objects'].type_desc AS 'Object Type Description'
FROM
	sys.sql_modules AS ['sys modules']
INNER JOIN
	sys.objects AS ['sys objects']
		ON
			['sys objects'].object_id = ['sys modules'].object_id
WHERE
	['sys modules'].definition like '%' + @SearchString + '%'