We can use a script like below to find all the tables in the database that have column with specified name in it:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'

Let us understand this with an example. In the below example, creating a demo database SQLHINTSDEMO with three tables Customer, Orders and Employee. And then search all the tables in the SQLHINTSDEMO database which have column name like CustomerId. It should return two table names Customer and Orders as both have column with column name like CustomerId.

CREATE DATABASE SQLHINTSDEMO
GO
USE SQLHINTSDEMO
GO
CREATE TABLE dbo.Customer(CustomerId INT, 
 CustomerName VARCHAR(100))
GO
CREATE TABLE dbo.Orders(OrderId INT,CustomerId INT)
GO
CREATE TABLE dbo.Employee(EmployeeId INT, 
 EmployeeName VARCHAR(100))
GO
SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
 INNER JOIN sys.columns c
 ON c.object_id = t.object_id
WHERE c.name like '%CustomerId%'
ORDER BY 'Table Name'
GO

RESULT