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.

Answer 2

DECLARE @name nvarchar(100)
-- for SQL Server 2008
SELECT * FROM sys.all_objects WHERE name LIKE @name AND type IN ('U', 'S')

-- for others versions
SELECT * FROM dbo.sysobjects WHERE type IN ('U', 'S') AND name LIKE @name

Both scripts are included in Microsoft's scripts.