Bulk Remove Database Objects From Database Project

Question

I'm currently maintaining a SQL Server database that is over 17 years old, and has a large number of unused and outdated artifacts within it.

Over the last several months we have been profiling the database to determine which of the 1800+ stored procedures are actually being used, and which ones we can safely remove. And as of right now, we have a list of about 1300 that we are planning on removing.

This database is also checked in under our source control in a SQL Server Database Project.

Now, what I'd like to do is generate a script to remove the 1300 procedures and apply that script to the database project first, before applying the script to our Development environment. But I can't seem to figure out a way to update the project via a script.

I've tried doing an Import > Script and importing a .sql file with DROP PROCEDURE commands, but it throws the following error whilst not removing the procedure from the project:

In the script that you provided to the import operation, one or more statements were not fully understood. These statements were moved to the ScriptsIgnoredOnImport.sql file. Review the file contents for additional information.

The contents of the file tested was the following:

Drop Procedure spProcedureName;

---

I could go through the project and remove each procedure one-by-one... but there are over 1300 to remove...

Is there any way to do a bulk update (via a script or otherwise) to a database project to remove multiple database objects?

---

Additional Information:

• Creating a shell database from the project and making updates to that will prove to be just as much effort as manually removing the 1,300 stored procedures one-by-one. This is due to the fact that many of the stored procedures we're planning on removing contain outdated/invalid references to tables, views, functions, OPENROWSET connections, and other procedures. Because of that, the deploy script fails, and a shell database cannot be created without modifying the individual invalid objects in the script.
• Getting a backup/restore of the current database is also not a viable solution, as the current database is a little over 3 TB in size.

Because of the above limitations, I am only looking for a solution that can be applied to the database project directly without any dependency on a secondary physical database.

Answer 1

1. Deploy your project to a new DB (DB_Temp)
2. Drop old stored procedures via TSQL
3. Create a new DB project and import the database schema from DB_Temp.
4. Update/check project settings, including Publish settings to drop SPs that are not in the project.
5. Deploy the project to DEV and Test environment, and test.
6. Drop the old project
7. Drop DB_Temp

UPDATE:

Alternative way:

1. Deploy your project to a new DB (DB_Temp)
2. Drop old stored procedures via TSQL
3. In VS, run Tools-->SQL Server-->Schema Comparison (Source: DB_Temp, Target: your project)
4. Press "Update" to remove the old SPs from the project
5. Deploy the project to DEV and Test environment, and test.
6. Drop DB_Temp