2

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.

Improve this question
6
  • The simplest is DROP PROCEDURE command listing SPs separated by a comma Sample Code Commented Jun 5, 2017 at 6:54
  • Else you can push the SP's name into a Temp table and then loop it using the query Commented Jun 5, 2017 at 7:00
  • @Joby My question isn't how to drop procedures from a database, but rather, how to drop procedures in bulk from a database project. Commented Jun 5, 2017 at 10:40
  • @Siyual How to Retrieve 1300 SP Which is not in Use Commented Jun 7, 2017 at 8:52
  • 1
    @AlfaizAhmed That is a bit too complicated to go over the specifics in a comment, but essentially, I wrote a service that periodically checked the recently used stored procedure stats and compiled those results over a series of months. Commented Jun 7, 2017 at 12:56

2 Answers 2

Reset to default
1
+250
  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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This is a possible solution, but I'd like to avoid this route if at all possible. I'd rather a solution that can be applied directly to a database project, without the dependency of a physical database.
I'm not sure there is a better way than this. You should be able to set your publish options to keep going even if there are failures or similarly create some shell DB. Drop the objects from that, then compare and remove from the project. If your DB project builds, you can do this against the (localdb) database instance to drop objects, then compare that to your project to remove.
Siyual, see updated answer as per Peter's advice - that way has fewer steps and simpler, but you still need to create a physical (temp) db. There is also programmatic way to delete the objects from a project - see another answer.
@PeterSchott Where is the option to continue if an error was encountered? I'm not seeing this option anywhere.
@Anton Unfortunately, this still doesn't work. It might be a bit of an edge case with my specific database, but even though it builds, it fails to deploy because of the invalid objects. I don't see a way to continue the deployment of an error was encountered, but if you know where that setting is, this solution would work.
|
0

To remove the object programmatically from a project, you may use EnvDTE.ProjectItem class, Remove() method.

https://msdn.microsoft.com/en-us/library/envdte.projectitem.remove.aspx

There are many VS extensions that can run EnvDTE commands, like this: https://vlasovstudio.com/visual-commander/index.html

Or you can use Package Management Console (Tools--> NuGet Package Manager --> Package Management Console)

Step1. Assign the list of SPs to variable:

$IncludeList = "SP1.sql", "SP2.sql"

Step2. Drop SP from the project if it is in the list.

If your project path to SPs is like "Database2\dbo\Stored Procedures\", then it's like:

$DTE.Solution.Projects | ForEach { if ($.Name -eq "Database2") {$.ProjectItems | ForEach { if ($.Name -eq "dbo") {$.ProjectItems | ForEach { if ($.Name -eq "Stored Procedures") {$.ProjectItems | ForEach {if ($IncludeList -contains $.Name) {$.Remove()}}}}}}}}

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.