A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.
I want to consolidate all these into a single database structure (not a single database - we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.
For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query
SELECT * FROM users
will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.
The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.
So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.
SELECT u.username, u.email, a.level FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID
If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?