How to select all tables with a particular name in database

Either sysobjects (where type='u'),
or (more correctly) the info-schemas:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%CUSTOMER%' -- or "='CUSTOMER'" for exact