I had to recreate a database in SQL Server the other day and my database user did not have privileges to recreate the database, so I found the following code which worked like a charm to drop all the tables in the database.  This example deletes all tables that start with PS, which drops all the PeopleSoft tables:

declare @SQL nvarchar(max)

SELECT @SQL = STUFF((SELECT ‘, ‘ + quotename(TABLE_SCHEMA) + ‘.’ + quotename(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘PS%’
FOR XML PATH(”)),1,2,”)

SET @SQL = ‘DROP TABLE ‘ + @SQL

PRINT @SQL

EXECUTE (@SQL) — uncomment to actually delete the tables