Monday, 18 June 2007

FEATURE REQUEST: VS DbPro - Refactor/Delete function

If you have been using Db Pro (aka DataDude) you may have hit this little headache during your build phase... if you delete a column, table, stored proc, function or other object, references to it are not deleted thus leaving your project in an error state which is simple to fix however time-consuming and unnecessary.

I've suggested this should be a prompted action whenever you do a schema view delete... and if the action is not carried out, then all affected items should be flagged.

If you've hit this little nugget of joy, please vote for the feature request...

************** UPDATE FROM MICROSOFT **************

This is a good suggestion, thank you for your feedback. We are considering your suggestion for future release.
Posted by Microsoft on 22/08/2007 at 9:45 AM
************** ANOTHER UPDATE FROM MICROSOFT **************
We are concerned about two potential problems around how we understand your suggestion:1) Cascading deletions. If we were to enable deletion of dependent objects automatically, the cascade could be disasterous2) Statement level references. Do you envision deletion of referencing statements within programmability objects or deletion of the programmability object (function, sproc, etc.) as a whole?We do believe that the deletion of child objects, such as indexes or triggers on a table being deleted, is beneficial.We'd be interested in additional details you could provide on the scenario that you envision
Posted by Microsoft on 8/22/2007 at 9:45 AM

I agree that a cascading delete would be far too heavy-handed. An excellent solution would be something similar to a 'schema compare'.
For example, let's say I select Refactor>Delete on an object called [Qty] on the [Order] table. A window appears looking very like the schema compare window which lists all objects dependent on [Order].[Qty] broken down by schema type. When an object is selected, the object definition is shown below with the references to [Order].[Qty] highlighted and below that the update script (and the cursor in the update script is positioned to the current object).
I have 3 Update Actions available to each object in the list - Skip, Drop and Update.When I select, Drop, the Update script is automatically updated to carry out that action.When I select Update, I then remove or replace references to [Order].[Qty] in the update script for the current object.When I select Skip, the object is not changed in any way.
Once all changes are done, I can then hit 'Write Updates' to apply the changes including the delete of [Order].[Qty].
This implies that a 'Find Orphaned References' should also exist which identifies references to deleted objects ie when the 'Skip' update action was selected. And again, a similar approach could be taken as outlined above.
This approach covers both your questions ie eliminating the impact of cascading deletes and amending programmability objects as well as deleting them.
Posted by khodges on 8/29/2007 at 5:33 PM


Anonymous said...

I came across your blog recently as I was doing some research on business intelligence. I had
a few questions and was wondering what would be the best way to get in contact with you to discuss a few of them?


Kristen Hodges said...

Hi Bobby,

I've sent you an email just now. If the questions seem relevant to everyone else who may be reading, I will add them as comments on this post.