Delete common objects from one database

On a test server, I copied certain functions, procs, tables and views from a big database to a smaller new app database. Now I'd like to remove most (but not all) of those objects from the big database. I run compare and get a nice list of everything in common. Can I create a script in SQL Compare to delete selected common objects from the larger database that have been recreated in the new app database?



Best Answer

  • MichaelFureyMichaelFurey Posts: 2 New member

    Thanks -- good idea. Wish I had done that approach. Or copied the whole database into a new one and deleted the objects I didn't need using Object Exporer Details to delete a bunch at a time.

    You can use SQL Compare to delete certain objects in one but not the other. Just not certain objects that are in both.

    I ended up doing it in SQL.


  • Hi @MichaelFurey unfortunately it's not possible to delete objects using SQL Compare. However I recommend the following steps to recreate your scenario in a cleaner manner!

    1. Create an empty database as your target
    2. Compare your "big" database to your newly created "modified big database" empty database
    3. Select all the objects you wish to keep from your original big database to your newly created big modified database and deploy 
    4. You can then use SQL Data Compare to deploy the data cross into your new database schema

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • Hi @MichaelFurey I believe what you're referring to is when a particular object in the source database doesn't exist in the target database and when you deploy it, it "deletes it" from the target. 

    Overall glad you managed to resolve your scenario with your approach! 

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

Sign In or Register to comment.