Delete common objects from one database

MichaelFurey

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?

thx

MichaelFurey

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.

DanC

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

DanC

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!