SQL Script to find SQL Objects containing specific text

Microsoft SQL ServerI 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 a Dynamics GP Consultant (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 + '%'