Find all tables, which includes column name

Get all table names for a specific column name

SELECT sysobj.name as Table_Name
FROM sysobjects sysobj
INNER JOIN syscolumns syscol
 ON sysobj.id= syscol.id
WHERE  syscol.name = 'COLUMN_NAME_FOR_SEARCH'

-------------------------------------------

Get all columns and table names for a 'like column_name' query

SELECT syscol.[name] as Column_Name,
 sysobj.name as Table_Name 
FROM sysobjects sysobj
INNER JOIN syscolumns syscol 
ON sysobj.id=syscol.id
WHERE syscol.name like '%COLUMN_NAME%'