2

I'm working with a system where each customer has their own database.

This amounts to ~4000 databases (approximately 150 tables each database) spread over 8 instances of Microsoft SQL server 2012.

The database scheme for all these databases is supposed to be identical, but recent events has shown that there are minor deviations in data types, indexes, keys and nullable/default value settings in some of the databases.

My goal is to synchronize all database schemas so they all have identical tables, columns, indexes, keys and nullable/default value settings

I have a single database that I'm certain has the 100% correct database schema.

How could I compare this database scheme to others, in an attempt to find variations in keys, indexes, tables, columns and nullable/default value settings?

I've read this question which is similar to mine, but the suggestions (like right clicking the database object in SSMS to generate a script or generating and comparing a .dacpac file with the Visual Studio extension SSDT) seems to be oriented towards comparing only two databases, and is not practical for comparing 4000 database schemas.

Daniel
  • 121
  • 4
  • 4
    Just be mindful that they may has been changed to be different by someone for a reason – Philᵀᴹ Jun 28 '18 at 14:57
  • A lot of tools can help with automating schema comparison, we have one: https://www.sentryone.com/products/pragmatic-workbench/doc-xpress/feature/metadata-comparison – Aaron Bertrand Jun 28 '18 at 15:02
  • I asked Paul to help me close this as a dupe instead of shopping list (after editing your question) as it would have been closed anyway. Maybe I should have opted to reopen after it was closed but that would have needed more votes. I'll add an answer about scripting the tools to the other question in the next few days and ping you here when I do – Tom V Jun 28 '18 at 17:28
  • Your question now has 3 reopen votes (one ow which Is mine) so we'll see what happens – Tom V Jun 28 '18 at 20:08
  • Have you gotten around to it yet @TomV ? – Daniel Jul 12 '18 at 07:27
  • @Daniel sorry I forgot about it, I did now. – Tom V Jul 12 '18 at 08:25
  • Sorry to keep you waiting, I completely forgot about this. – Tom V Jul 12 '18 at 08:43

0 Answers0