It can be done (and indeed has been done), but it's not a feature of SQL Server and it's not particularly 'light'.
A relational database management system stores all its data in tables (that's item 0 in Codd's 12 rules). The flexibility you describe comes when you define tables that define 4 things: tables, rows, columns and values.
- The 'tables' table lists all your tables (table id, attributes).
- The 'columns' table lists columns in all your tables (table id, column id, attributes).
- The 'rows' table lists rows in all your tables (table id, row id).
- The 'values' table lists values in all your tables (table id, row id, column id, value). if you don't want all strings, you can have multiple 'values' tables.
- There might be some other lookup tables for attributes and types, depending on how fancy you want to get.
You access the data by generating some rather fancy SQL that joins the 4 tables into the virtual table you mentioned.
Possibly not what you had in mind, but it can do what you asked.
And yes, this is an inner platform and has some features in common with a God table. As mentioned in these references, some very well known platforms use this technique to a greater or lesser extent, especially when tables, fields and relationships have to be defined on the fly. Microsoft CRM and Oracle Financials come to mind. Just don't do it unless you really need to. Then have no fear.
Obviously a system built like this would be next to impossible to query directly in SQL. As a rule end-users of such systems do not write SQL, but instead use the query and reporting tools provided as part of the system. It is also possible for the system to generate views on the fly to present the internal tables in a standard relational format, on which you can write standard SELECT queries and use SQL-oriented reporting tools.