NickName:Siyual Ask DateTime:2017-06-03T03:53:48

Bulk Remove Database Objects From Database Project

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.

Copyright Notice：Content Author:「Siyual」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/44336493/bulk-remove-database-objects-from-database-project

Answers

Anton 2017-06-05T07:10:23

\nDeploy your project to a new DB (DB_Temp)\nDrop old stored procedures via TSQL\nCreate a new DB project and import the database schema from DB_Temp.\nUpdate/check project settings, including Publish settings to drop SPs that are not in the project.\nDeploy the project to DEV and Test environment, and test.\nDrop the old project\nDrop DB_Temp\n\n\nUPDATE:\n\nAlternative way:\n\n\nDeploy your project to a new DB (DB_Temp)\nDrop old stored procedures via TSQL\nIn VS, run Tools-->SQL Server-->Schema Comparison (Source: DB_Temp, Target: your project)\nPress \"Update\" to remove the old SPs from the project\nDeploy the project to DEV and Test environment, and test.\nDrop DB_Temp\n",