SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema Comparison Across 8000 databases


Schema Comparison Across 8000 databases

Author
Message
pamozer
pamozer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1259 Visits: 443
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15027 Visits: 14396
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28411 Visits: 39963
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!

pamozer
pamozer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1259 Visits: 443
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search