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