This worked really well in my case where I had to delete several tables created my me in a specific database and I didnt want to write multiple queries to drop all tables one by one.
Use TABLESV in the event you have table names > 30 chars.
SyntaxEditor Code Snippet
SELECT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' FROM DBC.TABLESV WHERE DATABASENAME='DATABASE_NAME' AND TABLEKIND = 'T' AND TABLENAME LIKE 'TABLE_PREFIX_%';