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.

Solution

(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.

Comments

[Ram Rachum]

Thanks! A bit of a "duh" moment there, I guess I just missed that checkbox.

[Mohamed Naguib]

in visual studio 2013 I can neither find publish nor deploy tab in project setting and when I drop database, there is no script generated when I drop a table although it works for creating tables .. any suggestions?

[Gabriel Rainha]

@Mohamed Right click the database project on Solution Explorer, select Publish, then click the Advanced button. On the new window, go to the Drop tab, check the first option: Drop objects in target but not in source. Just be aware that now everything not in source will be dropped by default, so you'll have to manually check which objects you need not to be dropped.

[Saad Awan]

@GabrielRainha, I checked the box" Drop objects in target but not in project", saved changes and deleted the table from source control, DACPAC file deployed on Azure databas servere but still deleted SPs and tables are still active, tried so many times but no luck