Post reply

Schema Comparison Across 8000 databases

  • March 26, 2013 at 10:36 am

    #291577

    So we have a customer model database that is used to create our additional customer databases. We have noticed over time that somehow the customer databases have gotten additional tables and stored procedures that have not been applied across the board.

    Does anyone know of a tool where you compare multiple databases at the same time to one database?

    In other words I have a server that has 300 databases and I want to compare the schemas on each database to the customer model.

    Any recommendations would be very helpful

  • March 26, 2013 at 11:22 am

    #1600916

    Never used it this way but redgate SQL Compare Pro with an Automation License might be worth a look.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 26, 2013 at 12:14 pm

    #1600931

    i spent a bit of time on this same thing before, and it really depends on the results you are looking for, and how deep you want to make them match.

    if you just want to be aware of differences, you could use an EXCEPT query against each database, comparing it to the "perfect" customer model, and for things like sys.tables + sys.columns.

    if you need the script to be generated to change the database to match the "perfect" customer model, that's where it gets tougher...

    there's functionally equivalent, and that gets involved;

    for example, views/functions/stored procedure definitions might be different due to white space and comments, but are actually the same ; in a programming language you can strip out comments, then white space, and compare the results to see if they really are the different or functionally the same.

    Also for table definitions , maybe you don't care about constraint names, but you do care about that the constraints exist; and whether there's the desire for exact matches, where renaming of matching-but-wrongly-named constraints is required.

    I've created my own crap-tastic app to do that for myself, and it gets bigger all the time as i peel back the next layer of making databases match to another.

    It makes me appreciate just how much effort and man hours Redgate must have poured into their Compare product.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 26, 2013 at 12:17 pm

    #1600932

    That's not a bad idea to find differences between objects. I'm concerned also about differences in stored procs but that is not as much of an issue.

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

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