Locate a specific word in a database Goal
Locate a specific word among tables in a database and display the name of the table and column in a list.
Problem
There are TOO many tables to locate a specific word in the database.
Info

Using SQL Server 2012
Every table use a 'schemaname.tablename'
This will not be fast (this took over a minute to produce 0 results in my local copy of AdventureWorks).

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';


DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (
   SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
     + N' WHERE ' + QUOTENAME(c.name) + N' LIKE @s
 )'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;


If you need to support sql_variant for some reason (I recommend against it), only a few changes are needed:

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';


DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' 
 + QUOTENAME(t.name) + N' WHERE CONVERT(
   NVARCHAR(MAX), ' + QUOTENAME(c.name) 
 + N') LIKE @s)'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,98,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;