Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schema Comparison Across 8000 databases Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 10:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:04 PM
Points: 1,066, Visits: 409
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
Post #1435611
Posted Tuesday, March 26, 2013 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
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
Post #1435633
Posted Tuesday, March 26, 2013 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:40 PM
Points: 12,880, Visits: 31,802
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1435650
Posted Tuesday, March 26, 2013 12:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:04 PM
Points: 1,066, Visits: 409
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.
Post #1435651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse