0

I run a piece of software where each customer has their own database. I have a master database with their setup as well as their database names which is a part of their config.

What I am struggling with is updating hundreds of databases every time there is a platform update. Is there a way that I can select distinct databases from the master database and then loop through one by one and update my update scripts?

Six of Eighteen
  • 27
  • 1
  • 5
  • What a good explanation of why you don't want to have separate databases and want to store information in large tables in a single database. Of course, might might have good reasons for separating the data (say security requirements). – Gordon Linoff Aug 19 '20 at 11:32
  • They are separate for security reasons as well as performance reasons. – Six of Eighteen Aug 20 '20 at 12:02
  • . . Zero reason to separate them for performance reasons. The performance is probably worse than a well-designed physical data model -- not to mention the time you are wasting on a routine maintenance task. – Gordon Linoff Aug 20 '20 at 14:23

1 Answers1

0

Yes, but it's not pretty. The easiest and cleanest way would be to do this in some kind of scripting language, outside the database engine - run a query to retrieve the connection details for all your client databases, and then loop through those to execute the update statement. This is relatively straightforward, it's easy to handle errors, and easy to test.

If you really want to do it in the database engine, you can use dynamic SQL. This is much harder to test, and error handling can be a bit of a pain.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52