Check for name of an object in all databases

With following script we can easy iterate all databases on instance and search inside routines (views, procedures, functions etc.) for name of some object i.e. in case we would like to rename linked server and we need to know where it is currently used.

For sure we can use this script to search for any string we are interested in.

DECLARE @ObjectName sysname
DECLARE @Command NVARCHAR(MAX)

SET @ObjectName = 'THE_OBJECT'

SET @Command = 
'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
	USE ? 

	SELECT DB_NAME()

	SELECT DB_NAME() [DbName], [o].[name] [ObjectName], [o].[type_desc] [ObjectType]
	FROM [sys].[sql_modules] [sm]
		INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [sm].[object_id]
	WHERE [definition] LIKE ''%' + @ObjectName + '%'' OR [definition] LIKE ''%\[' + @ObjectName + '\]%'' ESCAPE ''\''
	ORDER BY [ObjectName]
END'

EXEC [sys].[sp_MSforeachdb] @Command
GO