DROP TABLE, DROP VIEW

These two commands let you remove tables and views from a database, as well as delete free tables. We could do all of this before with other commands, but DROP is the ANSI-standard keyword for removing things.

Usage

<a name=Usage>DROP TABLE TableName | FileName | ? [ RECYCLE ]</a>
DROP VIEW ViewName

The syntax for DROP TABLE reminds us of George Carlin’s immortal question, “Why are there three?” The answer is that you can delete a table that’s contained in a database by simply giving the table’s name, or delete a FREE TABLE by specifying its path and name or avoid having to remember names by specifying ?.

DROP TABLE changes its behavior depending on whether there’s a current database. If a database is set, it assumes you’re handing it the name of a table in the database (or want to choose one, if you put ?). If you specify a FREE TABLE in that case, you get an error message, even if you give its complete path and file name. The command just isn’t smart enough to understand that you couldn’t care less about the OPEN DATABASE.

When you issue DROP TABLE ?, if there’s a database set, you get a little dialog box that lets you choose a table from the database. If no database is current, you get the standard Windows Save dialog with a caption of “Delete” and the list limited to tables. Pretty smart. Of course, it would be even nicer if you could switch from database-container tables to free tables or choose a database and then see its tables.

The RECYCLE clause gives you the chance to go back again, sort of. If you add it, the table (and its associated index and memo files) lands in the Recycle Bin rather than going off into file heaven. You can restore them in the usual way. However, the result is as if you’d removed the table from the database with the REMOVE TABLE command—all the stuff that applies only to database tables (like rules and triggers and format and InputMask, and so forth) is lost.

In VFP 5, if the table you specify is the parent of a persistent relation, VFP's reaction to DROP TABLE depends on the case you use in the command, of all things. If you specify the table in all lowercase, instead of removing it from the database and deleting it, VFP gives you an error telling you it's "referenced" in a relation. Yeah, so what? But wait, it gets better. If you include even one uppercase letter in the name, the DROP TABLE command works. The table and relation are removed from the database, and the table is deleted. In VFP 6 and later, the situation is much simpler. It just doesn't let you drop a table that's referenced in a relation. We're not sure if that's a good thing or a bad thing, but we know for sure that it's not a documented thing.

DROP VIEW is a lot easier to deal with. Give it a view name in the current database and it removes the view from the database. There’s no RECYCLE clause here, and unlike choosing a view and pressing Delete in the Database Designer, there’s no confirmation dialog, so be careful.

Help says DROP VIEW is just another name for DELETE VIEW. So why doesn't DROP VIEW accept ? to let us choose a view to delete? DELETE VIEW does.

In VFP 7, dropping a table belonging to a database that has Database Events turned on fires the BeforeDropTable and AfterDropTable events. As you can probably guess, the BeforeDropView and AfterDropView events fires when a view is dropped.

Example

<a name=Example>* Careful - these commands will remove data from the sample </a>
* applications - try this on a copy!
OPEN DATABASE TasTrade
DROP TABLE Setup
SET DATA TO
DROP TABLE \MyData\MyTable
SET DATA TO TasTrade
DROP VIEW top25cust

See Also

Add Table, AfterDropTable, AfterDropView, BeforeDropTable, BeforeDropView, Create SQL View, Delete File, Delete View, Erase, Remove Table