T-SQL: Complete Database in one table
Congratulations, someone has reinvented EAV (Entity Attribute Value).  Please study up on the subject with them.

The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them.

I would also ask: If someone wants to put everything in a single EAV, for what purpose does someone still want to use SQL Server?  What benefits does SQL Server provide at that point over other approaches, particularly transactional databases (one example is a simple structure where you have "key columns" then "data columns" and you always look up data via the key).

Wikipedia has a reasonable entry.

A couple interesting notes from Wikipedia (the second of which applies in particular to the "only one Value column in the whole database" idea you listed someone as having proposed):


an index on the value column of an EAV table is essentially useless
constant data type inter-conversions are required if one wants to do anything with the values


Tony Andrews covers EAV as well as OTLT with an example of an EAV query that gets a bit cumbersome.

Aaron Bertrand argues for the EAV approach in select cases.

Aaron Bertrand has perhaps the best bullet list:

Cons from Aaron:


it is difficult to control the attribute names, and to make them consistent;
it is difficult to enforce data type integrity and referential integrity;
it is difficult (and slow) to pivot and/or self-join the values to make a single row;
it is difficult to make certain attributes mandatory; 
and, we've been trained to view this as the "wrong" way to solve the problem.


Pros from Aaron:


But what do you do when you have customers that demand real-time, on-demand addition of attributes that they want to store?