Dropping a table with Visual Studio 2010 Database Project

Question

I'm just starting to work with database projects in Visual Studio 2010, and I'm wondering how I get the differential script generator to include a DROP TABLE statement. If I create a new table script in the Tables folder, the differential picks that up and includes a CREATE TABLE statement in the deployment script, but when I delete that SQL file from the project, it doesn't put a DROP in the script.

I've verified that this applies to stored procedures as well. Is there any way to get rid of existing objects in the database?

Note: I think this would apply to Visual Studio 2008 Team System for Database Developers (Data Dude) GDR as well.

Answer 1

(These instructions apply to VS 2008 GDR2, but I believe as you said, these will be similar if not identical for VS 2010.)

You can open the project settings and go to the Deploy tab and check the box labeled "Generate DROP statements for objects that are in the target database but that are not in the database project".

Depending on how you work, you may also need to uncheck the box labeled "Block incremental deployment if data loss might occur". Fair warning, though, that this is a risky thing to do, and you might want to have it back up the database automatically before deployment while you're in there.