You can use sp_MSForeeachdb

This approach will gather INFORMATION_SCHEMA.COLUMNS for each database on your server

Select * Into #TempFields From INFORMATION_SCHEMA.COLUMNS where 1=0

Declare @SQL varchar(max)=';Use [?]; Insert Into #TempFields Select * From INFORMATION_SCHEMA.COLUMNS '     
Execute master.sys.sp_MSforeachdb @SQL
Select * from #TempFields
 Where Column_Name Like '%XYZ%'

EDIT - Requested Commentary

The First query creates an EMPTY Structure to hold the Information_Schema

Then we DECLARE @SQL which contains the SQL we want to execute for each database. Notice the ;Use [?];

Then we simply select the #Temp table for the desired results

You would need to iterate the databases on the server,

SELECT name FROM master.dbo.sysdatabases

performing the following command for each one, using

select table_name,COLUMN_NAME 
from INFORMATION_SCHEMA.columns 
where COLUMN_NAME like '% XXX %'

Doing it with a cursor

CREATE TABLE #tmpList (DatabaseName varchar(64),TableName varchar(64))
    DECLARE @DbName varchar(64)
    DECLARE @sql nvarchar(max)

    DECLARE @ColCursor  CURSOR

    SET @colCursor = CURSOR FOR
        SELECT [name] FROM master.dbo.sysdatabases

    OPEN @ColCursor
    FETCH NEXT FROM @ColCursor INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @sql = 'INSERT INTO #tmpList '+
                    'SELECT '''+@DbName+''',table_Name FROM '+@dbName+'.INFORMATION_SCHEMA.columns where COLUMN_NAME like ''% XXX %'' '
        EXEC(@sql)
        FETCH NEXT FROM @ColCursor INTO @DBName 
    END
    CLOSE @ColCursor;

select * from #tmpList
drop table #tmpList

run the following on each database, or using cursor and dynamic sql for iterating each database

select A.name,B.name From sys.objects as A 
inner join sys.all_columns as B
on B.object_id = A.object_id

where A.type = 'U' and B.name = ...

update: for fuzzy lookup, use B.name like '%serarch_pttern% , % means any leading or trailing characters

@Tim I agree with John. Here is an example of the code I used yesterday to find out somewhat similar to what your requirement is. This chunk of code will produce a list of tables and columns with that specific string within the data base you choose to run this query on.

select 
    o.name as [Table_Name]
    ,c.name as [Column_Name]
    ,o.create_date as Creation_Date
    ,o.modify_date as Modify_Date
    ,o.object_id
    ,is_nullable
    from sys.columns c
    inner join sys.objects  o on c.object_id=o.object_id
    -- where c.name like '%Discharge_Date%' specific column
    where o.name like '%Stg_Shibs%'-- string for example.
    order by o.name,c.name