-- Search all tables in current db scope with 'Customer' in the name
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Customer%'
GO

-- Search all columns in the current db scope with 'Customer' in the name
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%Customer%'
GO

-- run in sql server management studio

-- use to create drop table commands (but not execute)
SELECT 'DROP TABLE ' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Customer%'
GO
-- copy the result set into query window & run to execute