O que há com o agrupamento de algumas colunas em sys.databases?

A palavra oficial da Microsoft:

Algumas das colunas que contêm strings predefinidas (como tipos, descrições do sistema e constantes) são sempre fixadas em um agrupamento específico – Latin1_General_CI_AS_KS_WS. Isso independe do agrupamento de instância/banco de dados. O motivo é que esses são metadados do sistema (não metadados do usuário) e basicamente essas strings são tratadas sem distinção entre maiúsculas e minúsculas (como palavras-chave, sempre latinas).

Outras colunas nas tabelas do sistema que contêm metadados do usuário, como nomes de objetos, nomes de colunas, nomes de índices, nomes de login, etc., usam a instância ou agrupamento de banco de dados. As colunas são agrupadas para o agrupamento adequado no momento da instalação do SQL Server em caso de agrupamento de instância e no momento da criação do banco de dados em caso de agrupamento de banco de dados.

Você perguntou (grifo meu):

Por que o agrupamento dessas colunas é definido estaticamente?

A razão pela qual algumas colunas são definidas estaticamente é para que as consultas não precisem se preocupar com o agrupamento de servidor ou banco de dados (mais importante: CaSe SenSiTIviTy) para funcionar corretamente. Esta consulta sempre funcionará independentemente do agrupamento:

SELECT * FROM sys.databases WHERE state_desc = N'ONLine';

Considerando que, se o agrupamento do servidor diferenciasse maiúsculas de minúsculas, a consulta acima retornaria 0 linhas, assim como:

  SELECT * FROM sys.databases 
  WHERE state_desc COLLATE Albanian_BIN = N'ONLine';

Por exemplo, se você instalar uma instância do SQL Server com SQL_Estonian_CP1257_CS_ASagrupamento, execute o seguinte:

SELECT name, collation_name 
FROM master.sys.all_columns
WHERE collation_name IS NOT NULL
AND [object_id] = OBJECT_ID(N'sys.databases');

Você verá estes resultados (ou algo semelhante, dependendo da sua versão do SQL Server):

name                            SQL_Estonian_CP1257_CS_AS
collation_name                  SQL_Estonian_CP1257_CS_AS
user_access_desc                Latin1_General_CI_AS_KS_WS
state_desc                      Latin1_General_CI_AS_KS_WS
snapshot_isolation_state_desc   Latin1_General_CI_AS_KS_WS
recovery_model_desc             Latin1_General_CI_AS_KS_WS
page_verify_option_desc         Latin1_General_CI_AS_KS_WS
log_reuse_wait_desc             Latin1_General_CI_AS_KS_WS
default_language_name           SQL_Estonian_CP1257_CS_AS
default_fulltext_language_name  SQL_Estonian_CP1257_CS_AS
containment_desc                Latin1_General_CI_AS_KS_WS
delayed_durability_desc         SQL_Estonian_CP1257_CS_AS

Agora, para demonstrar exibições de metadados que herdam o agrupamento do banco de dados, em vez de herdar o agrupamento do servidor do banco de dados mestre:

CREATE DATABASE server_collation;
GO
CREATE DATABASE albanian COLLATE Albanian_BIN;
GO
CREATE DATABASE hungarian COLLATE Hungarian_Technical_100_CS_AI;
GO

SELECT name, collation_name 
  FROM server_collation.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM albanian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM hungarian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

Resultados:

server_collation
----------------
name                                 SQL_Estonian_CP1257_CS_AS
collation_name                       SQL_Estonian_CP1257_CS_AS
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  SQL_Estonian_CP1257_CS_AS


albanian
----------------
name                                 Albanian_BIN
collation_name                       Albanian_BIN
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Albanian_BIN


hungarian
----------------
name                                 Hungarian_Technical_100_CS_AI
collation_name                       Hungarian_Technical_100_CS_AI
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Hungarian_Technical_100_CS_AI

Assim, você pode ver que, neste caso, várias colunas herdam o agrupamento do banco de dados, enquanto outras são corrigidas para este agrupamento Latin1 "genérico", o que significa que ele é usado para isolar certos nomes e propriedades de problemas de diferenciação de maiúsculas e minúsculas, conforme descrito acima.

Se você tentar executar um UNION, por exemplo:

SELECT name FROM albanian.sys.columns
UNION ALL
SELECT name FROM server_collation.sys.columns;

Você recebe este erro:

Msg 451, Nível 16, Estado 1
Não é possível resolver o conflito de agrupamento entre "Albanian_BIN" e "SQL_Estonian_CP1257_CS_AS" no operador UNION ALL que ocorre na coluna 1 da instrução SELECT.

Da mesma forma, se você tentar executar um PIVOTou UNPIVOT, as regras serão ainda mais rígidas (todos os tipos de saída devem corresponder exatamente em vez de serem meramente compatíveis), mas a mensagem de erro é muito menos útil e até enganosa:

Msg 8167, Level 16, State 1
The type of column "column name" conflicts with the type of other columns specified in the UNPIVOT list.

You need to work around these errors using explicit COLLATE clauses in your queries. For example, the union above could be:

SELECT name COLLATE Latin1_General_CI_AS_KS_WS
  FROM albanian.sys.columns
UNION ALL
SELECT name COLLATE Latin1_General_CI_AS_KS_WS
  FROM server_collation.sys.columns;

The only time this may cause issues is you'll get confusing output if a collation is forced but doesn't contain the same character representation, or if sorting is used and the forced collation uses a different sort order than the source.

Background on Collation Precedence

The behavior you are seeing with regards to the Collation of various fields in the system catalog views is a result of how each field is defined and Collation Precedence.

When looking at sys.databases, it is important to keep in mind that it is not a table. While in the past (I think ending at SQL Server 2000) these were system catalog tables, they are now system catalog views. Hence, the source of the information in them is not necessarily coming from the current database context (or the context of the specified database when dealing with a fully-qualified object such as master.sys.databases).

Dealing specifically with sys.databases, some of the fields are coming from the [master] database (which was created with a collation based on the instance's default collation -- i.e. server-level collation), some of the fields are expressions (i.e. CASE statements), and some are coming from a "hidden" source: the [mssqlsystemresource] database. And the [mssqlsystemresource] database has a collation of: Latin1_General_CI_AS_KS_WS.

The name field is sourced from the name field in master.sys.sysdbreg. So this field should always be in the collation of the [master] database, which again will match the server's collation.

BUT, the following fields in sys.databases come from the [name] field in [mssqlsystemresource].[sys].[syspalvalues]:

• user_access_desc
• snapshot_isolation_state_desc
• recovery_model_desc
• page_verify_option_desc
• log_reuse_wait_desc
• containment_desc

Those fields should always have a collation of Latin1_General_CI_AS_KS_WS.

The collation_name field, however, comes from the following expression:

CONVERT(nvarchar(128),
        CASE
            WHEN serverproperty('EngineEdition')=5
                   AND [master].[sys].[sysdbreg].[id] as [d].[id]=(1)
              THEN serverproperty('collation')
            ELSE collationpropertyfromid(
                           CONVERT(int,
                            isnull([master].[sys].[sysobjvalues].[value] as [coll].[value],
                                   CONVERT_IMPLICIT(sql_variant,DBPROP.[cid],0)
                                ),
                         0),'name')
         END,
        0)

This is where Collation Precedence starts to come in. Both options for output here are system functions: serverproperty() and collationpropertyfromid(). The collation of this expression is considered a "Coercible-default":

Any Transact-SQL character string variable, parameter, literal, or the output of a catalog built-in function, or a built-in function that does not take string inputs but produces a string output.

If the object is declared in a user-defined function, stored procedure, or trigger, the object is assigned the default collation of the database in which the function, stored procedure, or trigger is created. If the object is declared in a batch, the object is assigned the default collation of the current database for the connection.

In light of that 2nd paragraph, since sys.databases is a view that exists in the master database, it takes on the collation of the master database (not the current database).

The state_desc field is also an expression:

CASE
   WHEN serverproperty('EngineEdition')=5
       AND [Expr1081]=(1)
       THEN N'RESTORING'
   ELSE
      CASE
         WHEN serverproperty('EngineEdition')=5
            AND CONVERT(bit,
                        [master].[sys].[sysdbreg].[status] as [d].[status]&(128),
                        0)=(1)
          THEN N'COPYING'
         ELSE
            CASE
               WHEN serverproperty('EngineEdition')=5
                  AND CONVERT(bit,
                              [master].[sys].[sysdbreg].[status] as [d].[status]&(256),
                              0)=(1)
                 THEN N'SUSPECT'
            ELSE [mssqlsystemresource].[sys].[syspalvalues].[name] as [st].[name]
            END
         END
       END

But, the collation on this expression is Latin1_General_CI_AS_KS_WS. Why? Well, something new is introduced in this expression: a reference to a real field: [mssqlsystemresource].[sys].[syspalvalues].[name] in that final ELSE clause. Column references are considered "Implicit":

A column reference. The collation of the expression is taken from the collation defined for the column in the table or view.

Of course, this opens up an interesting question: is it possible for this expression to return a different collation depending on how the CASE is evaluated? The literals will be in the collation of the database where this object is defined, but the ELSE condition returns a field value that should retain its original collation. Fortunately, we can simulate a test using the sys.dm_exec_describe_first_result_set Dynamic Management Function:

-- Force ELSE condition
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = -1;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE [name]
       END AS [Stuff]
FROM msdb.dbo.sysjobs
', NULL, NULL) rs

-- Force WHEN condition
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = 100;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE [name]
       END AS [Stuff]
FROM msdb.dbo.sysjobs
', NULL, NULL) rs

-- Control test
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = 100;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE N''Whazzup, yo?!?!?''
       END AS [Stuff]
', NULL, NULL) rs

Returns (on an instance set up with a collation of SQL_Latin1_General_CP1_CI_AS but running in a database with a collation of Japanese_Unicode_CI_AS):

system_type_name    max_length    collation_name
----------------    ----------    --------------
nvarchar(128)       256           SQL_Latin1_General_CP1_CI_AS
nvarchar(128)       256           SQL_Latin1_General_CP1_CI_AS
nvarchar(23)         46           Japanese_Unicode_CI_AS

Here we see that the two queries that reference the field in [msdb] take on the collation of the [msdb] database (which, being a system DB, was determined by the server collation).

Relating back to the Original Question

The observed behavior is that the following fields do not observe either the server collation, or the database collation; they are always presented in Latin1_General_CI_AS_KS_WS collation.

Your observation is spot-on: those fields always have a collation of Latin1_General_CI_AS_KS_WS, regardless of server collation or database collation. And the reason is: Collation Precedence. Those fields come from a table in the [mssqlsystemresource] database, and will retain that initial collation unless overridden with an explicit COLLATE clause since that has the highest precedence:

Explicit = An expression that is explicitly cast to a specific collation by using a COLLATE clause in the expression.

Explicit takes precedence over implicit. Implicit takes precedence over Coercible-default:
Explicit > Implicit > Coercible-default

And the related question:

Why is the collation of these columns statically set?

It is not that they are statically set, nor that the other fields are somehow dynamic. All of the fields in all of those system catalog views are operating on the same rules of Collation Precedence. The reason that they appear to be more "static" than the other fields (i.e. they do not change even if you install SQL Server with a different default collation, which in turn creates the system databases with that default collation) is that the [mssqlsystemresource] database consistently has a collation of Latin1_General_CI_AS_KS_WS across any installation of SQL Server (or so it certainly appears). And this makes sense because otherwise it would be difficult for SQL Server to manage itself internally (i.e. if sorting and comparison rules used for internal logic changed based on installation).

How to see these specifics yourself

If you want to see the source of any field(s) in any of these system catalog views, just do the following:

1. In a query tab in SSMS, enable the Query Option of "Include Actual Execution Plan" (CTRL-M)

2. Execute a query selecting one field from one of the system catalog views (I recommend selecting just one field at a time since the execution plan is ridiculously large / complex for even just a single field, and will include references to many fields that you aren't selecting):

   SELECT recovery_model_desc FROM sys.databases;
   

3. Go to the "Execution plan" tab
4. Right-click in the graphical Execution plan area and select "Show Execution Plan XML..."
5. A new tab in SSMS will open with a title similar to: Execution plan.xml
6. Go to the Execution plan.xml tab
7. Look for the first occurrence of an <OutputList> tag (it should be between lines 10 and 20 usually)
8. There should be a <ColumnReference> tag. The attributes in that tag should either point to a specific field in a table, or point to an expression defined later in the plan.

9. If the attributes point to a real field then you are done as it has all of the info. The following is what shows up for the recovery_model_desc field:

   <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]"
                    Table="[syspalvalues]" Alias="[ro]" Column="name" />
   

10. If the attributes point to an expression, such as if you instead selected the state_desc field, then you will initially find:

    <ColumnReference Column="Expr1024" />
    

11. In this case, you need to look through the rest of the plan for the definition of Expr1024 or whatever # it comes up with. Just keep in mind that there could be several of these references, but the definition won't be in an <OutputList> block. However, it will have a <ScalarOperator> sibling element that contains the definition. The following is what shows up for the state_desc field:

    <ScalarOperator ScalarString="CASE WHEN serverproperty('EngineEdition')=5 AND [Expr1081]=(1) THEN N'RESTORING' ELSE CASE WHEN serverproperty('EngineEdition')=5 AND CONVERT(bit,[master].[sys].[sysdbreg].[status] as [d].[status]&amp;(128),0)=(1) THEN N'COPYING' ELSE CASE WHEN serverproperty('EngineEdition')=5 AND CONVERT(bit,[master].[sys].[sysdbreg].[status] as [d].[status]&amp;(256),0)=(1) THEN N'SUSPECT' ELSE [mssqlsystemresource].[sys].[syspalvalues].[name] as [st].[name] END END END">
    

The same can be done to check the source of database-level catalog views as well. Doing this for an object like sys.tables will show that the name field comes from [current_db].[sys].[sysschobjs] (which is why it has a collation matching the database's collation), while the lock_escalation_desc field comes from [mssqlsystemresource].[sys].[syspalvalues] (which is why it has a collation of Latin1_General_CI_AS_KS_WS).

Clippy says, "It looks like you want to do an UNPIVOT query."

Now that we know why what Collation Precedence is and how it works, let's apply that knowledge to the UNPIVOT query.

For an UNPIVOT operation, SQL Server seems to really prefer (meaning: requires) that each source field be of the exact same type. Usually "type" refers to the base type (i.e. VARCHAR / NVARCHAR / INT / etc) but here SQL Server is also including the COLLATION. This should not be seen as unreasonable given what Collations control: the character set (i.e. Code Page) for VARCHAR, and the linguistic rules that determine equivalence of characters and combinations of characters (i.e. normalization). The following is a mimi-primer on what Unicode "normalization" is:

PRINT '---';
IF (N'aa' COLLATE Danish_Greenlandic_100_CI_AI = N'å' COLLATE Danish_Greenlandic_100_CI_AI)
     PRINT 'Danish_Greenlandic_100_CI_AI';
IF (N'aa' COLLATE SQL_Latin1_General_CP1_CI_AI = N'å' COLLATE SQL_Latin1_General_CP1_CI_AI)
     PRINT 'SQL_Latin1_General_CP1_CI_AI';
PRINT '---';
IF (N'of' COLLATE Upper_Sorbian_100_CI_AI =  N'öf' COLLATE Upper_Sorbian_100_CI_AI)
     PRINT 'Upper_Sorbian_100_CI_AI';
IF (N'of' COLLATE German_PhoneBook_CI_AI =  N'öf' COLLATE German_PhoneBook_CI_AI)
     PRINT 'German_PhoneBook_CI_AI';
PRINT '---';

Returns:

---
Danish_Greenlandic_100_CI_AI
---
Upper_Sorbian_100_CI_AI
---

So now let's start your original query. We will do a few tests to see how various changes alter the outcome, and then we will see how just a few changes can fix it.

1. The first error is about the CompatibilityLevel field, which is the second field to be unpivoted, and just happens to be an expression containing all string literals. With no field references, the resulting collation is deemed a "coercible-default"). Coercible-defaults take on the collation of the current database, let's say SQL_Latin1_General_CP1_CI_AS. The next 20 or so fields are also only string literals and hence are also coercible-defaults, so they shouldn't be in conflict. But if we look back to the first field, recovery_model_desc, that is coming directly from a field in sys.databases, which makes it an "implicit" collation, and this does not take on the local DB's collation, but instead retains it's original collation, which is Latin1_General_CI_AS_KS_WS (because it is really coming from the [mssqlsystemresource] DB).

   So, if field 1 (RecoveryModel) is Latin1_General_CI_AS_KS_WS, and field 2 (CompatibilityLevel) is SQL_Latin1_General_CP1_CI_AS, then we should be able to force field 2 to be Latin1_General_CI_AS_KS_WS to match field 1, and then the error should appear for field 3 (AutoClose).

   Add the following to the end of the CompatibilityLevel line:
   COLLATE Latin1_General_CI_AS_KS_WS

   And then run the query. Sure enough, the error now states that it is the AutoClose field that has the conflict.

2. For our second test we need to undo the change we just made (i.e. remove the COLLATE clause from the end of the CompatibilityLevel line.

   Now, if SQL Server is truly evaluating in the order in which the fields are specified, we should be able to remove field 1 (RecoveryModel), which will cause the current field 2 (CompatibilityLevel) to be the field that sets the master collation of the resulting UNPIVOT. And the CompatibilityLevel field is a coercible-default which takes on the database collation, so the first error should be the PageVerify field, which is a field reference, which is an implicit collation retaining the original collation, which in this case is Latin1_General_CI_AS_KS_WS and which is not the collation of the current DB.

   So go ahead and comment out the line starting with , RecoveryModel in the SELECT (towards the top) and then comment out the RecoveryModel line in the UNPIVOT clause below and remove the leading comma from the following line for CompatibilityLevel so that you don't get a syntax error.

   Run that query. Sure enough, the error now states that it is the PageVerify field that has the conflict.

3. For our third test, we need to undo the changes we just did to remove the RecoveryModel field. So go ahead and put back the comma, and uncomment those two other lines.

   Now we can go the other direction with forcing a collation. Rather than changing the collation of the coercible-default collation fields (which is most of them), we should be able to change the implicit collation fields to that of the current DB, right?

   So, much like our first test, we should be able to force the collation of field 1 (RecoveryModel) with an explicit COLLATE clause. But if we specify a particular collation and then run the query in a database with a different collation, the coercible-default collation fields will pick up the new collation which will then conflict with what we are setting this first field to. That seems like a pain. Fortunately, there is a dynamic way to deal with this. There is a pseudo collation called DATABASE_DEFAULT that picks up the current databases collation (just like the coercible-default fields do).

   Go ahead and add the following to the end of the line, towards the top, that starts with , RecoveryModel: COLLATE DATABASE_DEFAULT

   Run that query. Sure enough, the error states, once again, that it is the PageVerify field that has the conflict.

4. For the final test, we don't need to undo any of the prior changes.

   All we need to do now to fix this UNPIVOT query is to add the COLLATE DATABASE_DEFAULT to the end of the remaining implicit collation fields: PageVerify and RestrictedAccess. While the Collation field is also an implicit collation, that field comes from the master database, which is typically in line with the "current" database. But, if you want to be safe so that this always just works, then go ahead add the COLLATE DATABASE_DEFAULT to the end of that field as well.

   Run that query. Sure enough, no errors. All it took to fix this query was adding COLLATE DATABASE_DEFAULT to end of 3 fields (required) and maybe 1 more (optional).

5. Optional Test: Now that we finally have the UNPIVOT query working correctly, change just one of any of the field definitions beginning with CONVERT(VARCHAR(50), to instead be 51, as in: CONVERT(VARCHAR(51),.

   Run the query. You should get the same The type of column "X" conflicts with the type of other columns specified in the UNPIVOT list. error that you got when it was only the collation that was not matching.

   Getting the same error for both datatype and collation mismatches is not specific enough to be truly helpful. So there is definitely room for improvement there :).

Note related to the Query more than to the specific Question about Collation:

Since all of the source fields are of the datatype NVARCHAR, it would be safer to CONVERT all output fields to NVARCHAR instead of VARCHAR. You might not be dealing with data at the moment that has any non-standard-ASCII characters, but the system meta-data does allow for them so converting to NVARCHAR(128) -- which is the largest max length of any of those fields -- at least guarantees that there wouldn't be a problem for you in the future, or for anyone else copying this code who might already have some of those characters in their system.

This is a workaround for the specific issue rather than a full answer to the question. You can avoid the error by converting to sql_variant rather than varchar(50):

DECLARE @dbname SYSNAME;
SET @dbname = DB_NAME();

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
    , [BaseType] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'BaseType')
    , [MaxLength] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'MaxLength')
    , [Collation] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'Collation')
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(sql_variant, d.recovery_model_desc)
        , CompatibilityLevel            = CONVERT(sql_variant, CASE d.[compatibility_level] WHEN 70 THEN 'SQL Server 7' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' ELSE 'UNKNOWN' END)
        , AutoClose                     = CONVERT(sql_variant, CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(sql_variant, CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(sql_variant, CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(sql_variant, CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(sql_variant, CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(sql_variant, CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(sql_variant, CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(sql_variant, CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(sql_variant, CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(sql_variant, CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(sql_variant, CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(sql_variant, CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(sql_variant, CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(sql_variant, CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(sql_variant, CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(sql_variant, CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(sql_variant, CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(sql_variant, CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(sql_variant, CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(sql_variant, CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(sql_variant, 'TRUE')
        , PageVerify                    = CONVERT(sql_variant, page_verify_option_desc  )
        , BrokerEnabled                 = CONVERT(sql_variant, CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(sql_variant, CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(sql_variant, CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(sql_variant, user_access_desc)
        , Collation                     = CONVERT(sql_variant, d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

Adicionei três colunas para obter informações sobre o tipo subjacente da OptionValuecoluna.

Se o cliente não puder manipular sql_variantdados, faça uma conversão final (nível superior) na unpvt.OptionValuecoluna, por exemplo, nvarchar(256).

Ok, então eu dei uma olhada

sp_helptext [sys.databases]

então quebrou de onde as colunas estavam vindo. Aqueles com o Latin1_General_CI_AS_KS_WSagrupamento são todos provenientes da tabela do sistema, sys.syspalvaluesque parece ser uma tabela de pesquisa genérica (é uma tabela do sistema, portanto, você terá que se conectar por meio do DAC para vê-la).

Meu palpite é que ele está definido Latin1_General_CI_AS_KS_WSpara lidar com quaisquer valores de pesquisa possíveis. Eu posso ver como isso seria irritante embora.

Outra maneira de ver a definição (originalmente fornecida por Max em um comentário) é:

SELECT ObjectSchema = s.name
    , ObjectName = o.name
    , ObjectDefinition = sm.definition
FROM master.sys.all_sql_modules sm
    INNER JOIN master.sys.system_objects o ON sm.object_id = o.object_id
    INNER JOIN master.sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'sys' 
    AND o.name = 'databases';`