Make report over different databases on sql server

[mmwindel]

Hi,

Say you have a sql server with several databases within, one database for each customer/client. These databases have the same tables, same structure etc. How would go about developing a report which extracts the same query from every databases and uses the data as one data source, knowing the number of databases might change periodically, new customers/customers leaving ?

I know this a large question, let me see if I can break it up:

• How to get a list of every database-name on a sql server
• How to loop over the different databases and run the same query on each of them
• Add the results from each query together
• How to do this dynamically, as the number of databases might change

I hope my questions are clear and consice, any help would be appreciated.

Thank you.

[littlemojopuppy]

First, I would wonder why you have a single database for each customer instead of one database with a field for customer to differentiate between them.

But...you should be able to access them individually using Power Query by using each database as a data source.  Add a field for customer to each table pulled from each database so that when you merge each query together you can still tell which customer the data applies to.  I don't believe that you can loop through them in Power Query, but someone who is stronger in that can offer guidance.

But again, your underlying is that you shouldn't be using separate databases for each customer...that is just creating problems for yourself.

[amitchandak]

@ImkeF , can you help on this