We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How would I dynamically generate views for tables in the database, which combine the data? The databases reside on the same server instance.
Many db professionals prefer single tenant databases.
Best Answer
Code provided below: CustomerTemplateDb contains all the tables for single tenant database.
It will list other single tenant databases around it, and create one consolidated view in reporting database.
(Feel free to code review, or make modifications as needed. Works on my end)