How to select all tables with a particular name in database

Question

How do I select all the tables name with a particular name in the database?

Answer 1

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

If you meant something different, please clarify.