Getting the "flat" query corresponding to a view using nested views?

Question

I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many interesting typos), and in some parts have quite bad database design.

To ease working with these, I use a large "view library" as I call it, which defines views upon views that bring a lot of clarity, eliminate repetition, and allow me to ultimately aggregate all data which my application needs in a specific screen into one or two views that the application needs to query.

This works very well and I'm happy with it.

(Yes really. The performance is usually great, and when it isn't it can usually be fixed without much hassle and is almost always revealed to be related to a bad join or sub-query somewhere that would have caused the issue regardless of whether views are being used or not. I've only ever had one issue related to using views per se, which was MS SQL Server failing to optimize queries against one of the most complex views --perhaps the most complex in fact-- which I was able to solve simply by turning the view into a procedure that materializes the results of a couple intermediate views first.)

Now in some new deployment variants of SAP Business One, I will lose the ability of creating arbitrary SQL views in the database. Bad news. I will however be allowed to run some read-only queries, via a limited subset of the SQL language...

This brought me to the question: is there a way to "flatten" an SQL view via MS SQL Server (any version), so that I automatically get a query that already has all nested views "resolved" to their definitions, recursively?

For instance, consider the following sequence of (highly simplified) view definitions:

create view myItems as
  select itemCode, itemName from Items

create view myCustomers as
  select customerCode, customerName from Customers

create view myOrders as
  select o.orderId, o.orderNumber, c.*
  from Orders o
  join myCustomers c on c.customerCode = o.customerCode

create view myOrderLines as
  select l.lineId, l.lineNumber, o.*, i.*
  from OrderLines l
  join myOrders o on o.orderId = l.orderId
  join myItems i on i.itemCode = l.itemCode

I want the ability to get the "flat version" of let's say myOrderLines, which would be this:

select l.lineId, l.lineNumber,
       o.orderId, o.orderNumber,
       c.customerCode, c.customerName,
       i.itemCode, i.itemName
from OrderLines l
join Orders o on o.orderId = l.orderId
join Customers c on c.customerCode = o.customerCode
join Items i on i.itemCode = l.itemCode

Is such a thing possible?

Answer 1

So I know this doesn't directly answer your question but I think are things to consider that may be of help:

Why do you lose the ability to create Views and would you be able to create a second database to house your Views instead? (Views can reference other databases besides the one they exist in.)

If the previous solution isn't possible, could you use a data synchronization feature like Replication or Availability Groups to copy the SAP database to another database where you would be able to create your Views (either on the same server or another server)?

After getting a better picture from your comments, you can still look into Replication to centralize the data into one database outside of SAP, that your product can work off of. Another feature in SQL Server useful for consolidating multiple sources of data into one destination is SSIS.