AEM/CQ5 SQL2 How do I find all occurrences of a node by name

The Problem

You want to use a SQL2 query to retrieve all nodes that are named "nameofnode"

The Solution

SELECT * FROM [nt:base] AS s WHERE LOCALNAME() LIKE "nameofnode"

The LOCALNAME() function returns the node name.