Deleting a Table
Sometimes you have to delete a table: for example, when you want to implement a new design or free space in the database. When you delete a table, its structural definition, data, full-text indexes, constraints, and indexes are permanently deleted from the database; and the space formerly used to store the table and its indexes is made available for other tables. You can explicitly drop a temporary table if you do not want to wait until it is automatically dropped.
If you have to delete tables that are related through FOREIGN KEY and UNIQUE or PRIMARY KEY constraints, you must delete the tables with the FOREIGN KEY constraints first. If you have to delete a table that is referenced in a FOREIGN KEY constraint but you cannot delete the whole foreign key table, you must delete the FOREIGN KEY constraint.
If you want to delete all the data in a table but do not want to delete the table itself, you can truncate it. For more information, see Deleting All Rows by Using TRUNCATE TABLE.
In SQL Server 2005, you can drop or truncate large tables and indexes that use more than 128 extents, without holding simultaneous locks on all the extents required for the drop. The SQL Server 2005 Database Engine manages large object drops by splitting them in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the allocation units marked for deallocation are physically dropped in batches. For more information, see Dropping and Rebuilding Large Objects.
To delete a table
To delete a FOREIGN KEY constraint
To truncate a table