SQL Server and TFS - How to rename stored procedures

Question

After a while one may wish to do some light refactoring, such as giving more appropriate names to stored procedures as their functionalities are better established.

For the life of me, I simply cannot rename anything in SQL in a multi-branch TFS environment without it being an onerous, manual process.

Without using some third-party tool, how are you supposed to do it?

_{P.S. This is just for renaming stuff in a way that doesn't require deployment scripting to migrate anything. That is its own giant can of worms.}

---

When I try to rename a SQL entity (e.g. a table, stored procedure), I use the Visual Studio Find and Replace in Files utility and then rename the file, since the super-useful refactor tools that I use all day for C# do not exist for SQL.

After enabling the advanced SSDT Publish Option to drop objects in target but not in source and manually verifying everything works (since the validations done by the build and the publish are severely lacking) and convincing myself my rename was implemented correctly, I now have to merge changes to a couple other branches in TFS.

Apparently TFS Power Tools Migrate of rename is bugged _{(but since Microsoft retired Connect, I can't follow up on that post)} because all my renames are now showing as ADD changes.

After undoing all those erroneous pending changes, the files are leftover on disk, so manually renaming the files throws file already existing errors. Upon deleting those orphaned files, I am back where I started with no automated tools to help, having to repeat all the changes in every target branch and having to manually detect then merge conflicts.

_{To add insult to injury, my %AppData%\Local\Microsoft\VisualStudio\14.0\ComponentModelCache folder keeps getting corrupted for some unknown reason (which persists through a complete repair reinstall) such that renaming files only under *.sqlproj throws a null reference error at me about a parameter name 'o' upon which the file is renamed on disk but not in the solution which then corrupts my TFS workspace.}

---

I don't know what is wrong with me, but it seems that renaming SQL entities is unofficially blocked by Microsoft in every possible devious way.

P.S. There is no "rename" tag to put on this post, further suggesting that renaming stuff is not something people ever do in databases.

Answer 1

This question isn't so much about how to RENAME a stored procedure, as it is asking about how to MERGE the RENAME to other branches in TFS.

As pointed out by Dan Guzman in the comments, the Refactor > Rename feature while editing a SQL file should be used:

(note that this is not an option if you right-click the file; you have to right click the entity name in the file _{(or have the caret on the name and use the shortcut chord)})

The hard part is now merging the rename between branches.

Using either the MERGE command from Source Control Explorer or the unshelve /migrate command to TFS Power Tools (tfpt.exe), the pending changes are generated as a DELETE and an ADD which deletes the link to the TFS history!

Furthermore, your .sqlproj file will be a giant headache to merge conflicts this way.

Instead, the renamed files should be manually renamed in the target branch, then the TFS Power Tools unshelve /migrate command will just require merging the file contents as normal, with the .sqlproj file already having the changes made to it (so you can choose the Keep target branch version option on it, presuming you don't have other ADD or DELETE changes additional to the RENAME changes).

_{There seems to be another method using labels, but the gated check-in process is a contraindication to it.}

Note that if you start getting this error message renaming files, immediately restart Visual Studio and manually fix the .sqlproj file in a text editor as the file was renamed on disk but not in the project file:

To recap, this is the process I have found to work the best:

1. Manually perform all the renames in the target branch
2. Ensure changes in the source branch are in a shelfset
3. Use the TFS Power Tools unshelve /migrate command
4. Verify. Verify. Verify. And also verify by using this advanced publish option:

I hope this helps somebody, as this process has been a serious headache for me because I have to merge to multiple branches, and my changeset is very large.