Cacher is the code snippet organizer for pro developers

/*
Source: https://stackoverflow.com/a/14985918
*/
DECLARE @SearchStr nvarchar(100) = N'foo' -- The search string

DECLARE @dml nvarchar(max) = N''        

DECLARE @DbSearchResults TABLE
 ([tablename] nvarchar(100), 
  [ColumnName] nvarchar(100), 
  [Value] nvarchar(max))  
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + 
                c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + 
               ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
               ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
                   JOIN sys.columns c ON t.object_id = c.object_id
                   JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')

INSERT @DbSearchResults
EXEC sp_executesql @dml

SELECT *
FROM @DbSearchResults

/*
Use this query to find the name of a database that has been renamed so you can change the name in all views, stored prcedures, functions, etc.
*/
DECLARE @Search varchar(255)
SET @Search='%Old_DB_Name%'

SELECT DISTINCT
    DB_NAME() as DBNAME, schema_name(o.schema_id) as SchName, o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1