Find data in each database (excluded system database)

Using SQL Server undocumented Stored Procedure (sp_msforeachdb) to locate data appear in any column in each database

Query

sp_msforeachdb ' DECLARE @SearchText VARCHAR(MAX) = ''a'' DECLARE @SQL VARCHAR(MAX) = ''SELECT [Database Name] = ''''?'''', [Schema Name] = TABLESCHEMA, [Table Name] = TABLENAME, [Column Name] = COLUMNNAME, [Occur Count] = SUM(CNT) FROM ('' + STUFF(             (SELECT     '' UNION ALL SELECT TABLESCHEMA = '''''' + TABLES.TABLE_SCHEMA + '''''', TABLENAME = '''''' + TABLES.TABLE_NAME + '''''', COLUMNNAME = CAST('''''' + COLUMN_NAME + '''''' AS VARCHAR(MAX)), CNT = 1 FROM ?.['' + TABLES.TABLE_SCHEMA + ''].['' + TABLES.TABLE_NAME + ''] WHERE '' + CASE WHEN DATA_TYPE = ''image'' THEN ''CAST('' ELSE '''' END + ''CAST(['' + COLUMN_NAME + '']'' + CASE WHEN DATA_TYPE = ''image'' THEN '' AS VARBINARY(MAX))'' ELSE '''' END + '' AS VARCHAR(MAX)) LIKE ''''%'' + @SearchText + ''%''''''             FROM  ?.INFORMATION_SCHEMA.TABLES                   JOIN ?.INFORMATION_SCHEMA.COLUMNS                   ON TABLES.TABLE_CATALOG = COLUMNS.TABLE_CATALOG                   AND TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA                   AND TABLES.TABLE_NAME = COLUMNS.TABLE_NAME             WHERE TABLES.TABLE_TYPE = ''BASE TABLE'' AND TABLES.TABLE_CATALOG NOT IN (''master'',''model'',''msdb'',''tempdb'')             FOR XML PATH(''''))             , 1, 11, '''') + '')Src GROUP BY TABLESCHEMA, TABLENAME, COLUMNNAME'' EXEC (@SQL)'

Output

Reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190619