How to compare 2 databases to find what objects are out of sync?

  • Goalie35

    SSC Eights!

    Points: 867

    Last night, my company tried deploying a large update to almost all tables & sp's to our production database (basically just adding a new field to each table & sp, but the qty of objects updated was pretty massive) but ran into problems because the script we tried to deploy to run all of these updates, was based off of our development database.  Ideally, these 2 db's should be in sync but we found several objects that were out of sync between the 2 db's, so when we tried to run the portion of the script that updated the SP's, it was trying to find tables & fields that didn't exist in production & thus producing errors.

    Before trying to run this in production again, we'd like to do a comparison between the 2 db environments and find ALL items that are out of sync between the 2.  Is there a query I can run or even a free 3rd party tool that can handle this?

    Thanks

  • anthony.green

    SSC Guru

    Points: 112470

    Visual Studio has a schema compare option if you have the database tools installed.  It's free but can be a bit clunky as it pulls in differences you tell it to ignore half the time.

    If you want to pay for good tools then take a look at Red-Gate SQL Compare and ApexSQL Diff

  • TUellner

    SSCrazy

    Points: 2574

    I'll add this. Redgate SQL Compare is worth every penny. And no, I don't work for them or get anything from them for saying this. I just love the tools.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply