How to Delete Stored Procedures from Database Project in Visual Studio

database-projectsvisual studio 2017

I have a Visual Studio database project with several stored procedures. I deleted one of them and then published the project. But the SP was still present on the published database and I had to delete manually.

I would want the VS project to be the point of truth for the database. But at the same time, I'm publishing to a production database that has data.

Is there a way to delete files in a VS database project so that those changes reflect on the published database?

Best Answer

The easiest way is using "Schema compare", where you would manually select objects to be dropped/ignored/added. to do this:

  1. Right click the database in the solution explorer (choose schema compare)
  2. Connect to your database
  3. Click the 'compare' button
  4. select/deselect differences changes that will be made
  5. click the 'update' button
  6. *note: if you want to NOT block on possible data loss, you need to configure that BEFORE comparing schemas. Do this by clicking the little settings icon

If you wish to DELETE (DROP) database objects in production database, that do not exist in your Database Project, you should successfully Build and Publish the project on production database using default settings.

If you have performed the Build successfully, proceed like this:

Check to see if this checkbox is deselected:

  1. In Solution Explorer, right-click your database and select "Publish".
    "Publish Database" window should open

  2. Click the "Advanced..." button.
    "Advanced Publish Settings" window should open

  3. Switch to "Drop" tab

  4. Deselect the checkbox "Do not drop stored procedures".
    Click OK and continue with publish.

"Advanced Publish Settings"

Related Question