Below statement will first generate drop statement then exec statement will drop those tables from said schema.

DECLARE @droptable NVARCHAR(MAX) SELECT @droptable = COALESCE(@droptable, N'') + 'DROP TABLE [DBA].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DBA' and TABLE_TYPE = 'BASE TABLE'

EXEC sp_executesql @droptable