how to identify table name using a column name if we are not aware about the table name
You can use INFORMATION_SCHEMA.COLUMNS instead of sys.columns

SELECT
    TABLE_CATALOG,TABLE_SCHEMA,TABLE_SCHEMA,COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    COLUMN_NAME LIKE '%MyColumn'