Demolition does not require any check
The wording is indeed ambiguous on its own, but a comparison with other Tool Proficiency benefits leads me to believe it would be explicit if a check were required. Here are some examples of proficiency benefits which require checks:
Decipher Treasure Map. This tool proficiency grants you expertise in examining maps. You can make an Intelligence check to determine a map’s age, whether a map includes any hidden messages, or similar facts.
Craft Hidden Compartment. With 8 hours of work, you can add a hidden compartment to a pair of shoes. The compartment can hold an object up to 3 inches long and 1 inch wide and deep. You make an Intelligence check using your tool proficiency to determine the Intelligence (Investigation) check DC needed to find the compartment.
Quick Fake. As part of a short rest, you can produce a forged document no more than one page in length. As part of a long rest, you can produce a document that is up to four pages long. Your Intelligence check using a forgery kit determines the DC for someone else’s Intelligence (Investigation) check to spot the fake.
Since several other proficiency benefits explicitly state when a check is needed, while I can find none which explicitly state that no check is needed, I believe the correct interpretation is that any benefit which doesn't mention a check does not require one.
How do I attack weak points if I haven't found them?
A potential new confusion arises: If I can find weak points to attack without a check, why is there also an option to find weak points in general which requires a check? The best answer I can find is that Demolition doesn't actually require that you attack weak points to gain the benefit, thus you don't need to find them. Demolition doubles damage to "such structures" as brick walls, which is significantly different wording to an otherwise similar benefit from Glassblower's tools:
Identify Weakness. With 1 minute of study, you can identify the weak points in a glass object. Any damage dealt to the object by striking a weak spot is doubled.
Since the Glassblower's tools benefit specifies "striking a weak spot" deals double damage, while Mason's tools do not specify that attacks must strike a weak spot to deal double damage, I would argue that using Demolition to double damage against a structure does not need you to actually find the weak points and so it doesn't overlap with the example ability check. As for what you would accomplish by finding weak points with the check if it's not necessary for the damage, I would say it still has broader roleplay uses. For example, perhaps you could find a weak point and then fortify it rather than destroying it. Or perhaps you could then direct other players or NPCs to attack the weak point, gaining a benefit even though you are not making a weapon attack yourself. Many of the suggested checks for various tools give information that doesn't have any inherent mechanical consequence, so it is normal that the player and DM need to make their own interpretations for how that information can be used.