O co chodzi z zestawieniem niektórych kolumn w sys.databases?

Próbuję uruchomić UNPIVOTna różnych kolumnach zawartych w sys.databasesróżnych wersjach SQL Server, od 2005 do 2012.

UNPIVOTSię niepowodzeniem z następującym komunikatem o błędzie:

Msg 8167, poziom 16, stan 1, wiersz 48

Typ kolumny „CompatibilityLevel” powoduje konflikt z typem innych kolumn określonych na liście UNPIVOT.

T-SQL:

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

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc)
        , CompatibilityLevel            = CONVERT(VARCHAR(50), 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(VARCHAR(50), CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(VARCHAR(50), CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(VARCHAR(50), CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(VARCHAR(50), CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(VARCHAR(50), CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  )
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc)
        , Collation                     = CONVERT(VARCHAR(50), 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;

Ma to na celu zapewnienie ładnie sformatowanej listy opcji bazy danych dla danej bazy danych, podobnie jak:

+----------+----------------------------+----------------------------+
| Database | Configuration Item         | Value in Use               |
+----------+----------------------------+----------------------------+
| master   | RecoveryModel              | SIMPLE                     |
| master   | CompatibilityLevel         | SQL Server 2008            |
| master   | AutoClose                  | FALSE                      |
| master   | AutoCreateStatistics       | TRUE                       |
| master   | AutoShrink                 | FALSE                      |
| master   | AutoUpdateStatistics       | TRUE                       |
| master   | AutoUpdateStatisticsAsynch | FALSE                      |
| master   | CloseCursorOnCommit        | FALSE                      |
| master   | DefaultCursor              | GLOBAL                     |
| master   | ANSINULL_Default           | FALSE                      |
| master   | ANSINULLS_Enabled          | FALSE                      |
| master   | ANSIPadding_Enabled        | FALSE                      |
| master   | ANSIWarnings_Enabled       | FALSE                      |
| master   | ArithmeticAbort_Enabled    | FALSE                      |
| master   | ConcatNullYieldsNull       | FALSE                      |
| master   | CrossDBOwnerChain          | TRUE                       |
| master   | DateCorrelationOptimized   | FALSE                      |
| master   | NumericRoundAbort          | FALSE                      |
| master   | Parameterization           | SIMPLE                     |
| master   | QuotedIdentifiers_Enabled  | FALSE                      |
| master   | RecursiveTriggers_Enabled  | FALSE                      |
| master   | TrustWorthy                | TRUE                       |
| master   | VARDECIMAL_Storage         | TRUE                       |
| master   | PageVerify                 | CHECKSUM                   |
| master   | BrokerEnabled              | FALSE                      |
| master   | DatabaseReadOnly           | FALSE                      |
| master   | EncryptionEnabled          | FALSE                      |
| master   | RestrictedAccess           | MULTI_USER                 |
| master   | Collation                  | Latin1_General_CI_AS_KS_WS |
+----------+----------------------------+----------------------------+

Gdy uruchomię to na serwerze z Latin1_General_CI_AS_KS_WSsortowaniem, instrukcja powiedzie się. Jeśli zmodyfikuję język T-SQL, aby niektóre pola zawierały COLLATEklauzule, będzie on działał na serwerach, które mają inne układy.

Kod działający na serwerach z innymi zestawieniami Latin1_General_CI_AS_KS_WSto:

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

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc) COLLATE SQL_Latin1_General_CP1_CI_AS
        , CompatibilityLevel            = CONVERT(VARCHAR(50), 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(VARCHAR(50), CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(VARCHAR(50), CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(VARCHAR(50), CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(VARCHAR(50), CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(VARCHAR(50), CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  ) COLLATE SQL_Latin1_General_CP1_CI_AS
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc) COLLATE SQL_Latin1_General_CP1_CI_AS
        , Collation                     = CONVERT(VARCHAR(50), 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;

Obserwowane zachowanie polega na tym, że następujące pola nie obserwują ani sortowania serwera, ani sortowania bazy danych; zawsze są prezentowane w Latin1_General_CI_AS_KS_WSzestawieniu.

W SQL Server 2012 możemy sys.sp_describe_first_result_setłatwo uzyskać metadane dotyczące kolumn zwróconych z określonego zapytania. Użyłem następujących do ustalenia niedopasowania zestawień:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
SELECT 
    DatabaseName                    = CONVERT(VARCHAR(50), d.name)
    , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc) 
    , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
FROM sys.databases d
WHERE name = DB_NAME();
';

EXEC sp_describe_first_result_set @command = @cmd;

Wyniki:

Dlaczego zestawianie tych kolumn jest ustawione statycznie?

Oficjalne słowo od Microsoft:

Niektóre kolumny, które zawierają wstępnie zdefiniowane ciągi znaków (takie jak typy, opisy systemu i stałe) są zawsze przymocowane do określonego zestawienia - Latin1_General_CI_AS_KS_WS. Jest to niezależne od sortowania instancji / bazy danych. Powodem jest to, że są to metadane systemowe (nie metadane użytkownika) i zasadniczo te ciągi są traktowane bez rozróżniania wielkości liter (jak słowa kluczowe, więc zawsze łacińskie).

Inne kolumny w tabelach systemowych, które zawierają metadane użytkownika, takie jak nazwy obiektów, nazwy kolumn, nazwy indeksów, nazwy logowania itp., Obejmują sortowanie instancji lub bazy danych. Kolumny są zestawiane do właściwego sortowania w momencie instalacji SQL Server w przypadku sortowania instancji oraz w momencie tworzenia bazy danych w przypadku sortowania bazy danych.

Zapytałeś (moje podkreślenie):

Dlaczego zestawianie tych kolumn jest ustawione statycznie?

Powodem, dla którego niektóre kolumny są ustawione statycznie, jest to, że zapytania nie muszą się martwić o sortowanie serwera lub bazy danych (co ważniejsze: ODPOWIEDZIALNOŚĆ), aby działać poprawnie. To zapytanie zawsze będzie działać niezależnie od sortowania:

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

Podczas gdy w sortowaniu serwera rozróżniana jest wielkość liter, powyższe zapytanie zwróci 0 wierszy, podobnie jak to:

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

Na przykład jeśli zainstalujesz wystąpienie programu SQL Server z SQL_Estonian_CP1257_CS_ASsortowaniem, uruchom następujące polecenie:

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

Zobaczysz te wyniki (lub coś podobnego, w zależności od wersji programu 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

Teraz, aby zademonstrować widoki metadanych, które dziedziczą sortowanie bazy danych, zamiast dziedziczenia sortowania serwera z głównej bazy danych:

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

Wyniki:

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

Widać więc, że w tym przypadku kilka kolumn dziedziczy sortowanie bazy danych, podczas gdy inne są przymocowane do tego „ogólnego” zestawienia Latin1, co oznacza, że ​​służy ono do izolowania niektórych nazw i właściwości od problemów z rozróżnianiem wielkości liter, jak opisano powyżej.

Jeśli spróbujesz wykonać UNIONna przykład:

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

Otrzymujesz ten błąd:

Msg 451, poziom 16, stan 1
Nie można rozwiązać konfliktu sortowania między „Albańskim_BIN” a „SQL_Estonian_CP1257_CS_AS” w operatorze UNION ALL występującym w kolumnie instrukcji SELECT 1.

Podobnie, jeśli starają się przeprowadzić PIVOTlub UNPIVOTprzepisy są nawet bardziej rygorystyczne (typy wyjściowe muszą cały mecz dokładnie , a nie tylko być zgodne), ale komunikat o błędzie jest znacznie mniej pomocne, a nawet mylące:

Msg 8167, poziom 16, stan 1
Typ kolumny „nazwa kolumny” jest w konflikcie z typem innych kolumn określonych na liście UNPIVOT.

Musisz obejść te błędy, używając COLLATEw swoich zapytaniach wyraźnych klauzul. Na przykład powyższy związek może być:

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;

Jedynym razem, gdy może to powodować problemy, otrzymasz mylące dane wyjściowe, jeśli sortowanie jest wymuszone, ale nie zawiera takiej samej reprezentacji znaków, lub jeśli sortowanie jest używane, a wymuszone sortowanie używa innej kolejności sortowania niż źródło.

Tło dotyczące pierwszeństwa sortowania

Zachowanie, które widzisz w odniesieniu do sortowania różnych pól w widokach katalogu systemowego, jest wynikiem tego, jak zdefiniowane jest każde pole i priorytet sortowania.

Patrząc na sys.databasesto, należy pamiętać, że nie jest to stół. Podczas gdy w przeszłości (myślę, że kończy się na SQL Server 2000) były to tabele katalogu systemowego , teraz są widokami katalogu systemowego . Dlatego źródło zawartych w nich informacji niekoniecznie pochodzi z bieżącego kontekstu bazy danych (lub kontekstu określonej bazy danych, gdy mamy do czynienia z w pełni kwalifikowanym obiektem, takim jak master.sys.databases).

W szczególności sys.databases, niektóre pola pochodzą z [master]bazy danych (która została utworzona z sortowaniem opartym na domyślnym sortowaniu instancji - tj. Sortowaniu na poziomie serwera), niektóre pola są wyrażeniami (tj. CASEInstrukcjami), a niektóre nadchodzą z „ukrytego” źródła: [mssqlsystemresource]bazy danych. A [mssqlsystemresource]baza danych ma sortowania: Latin1_General_CI_AS_KS_WS.

namePole jest pozyskiwane z namepolu master.sys.sysdbreg. To pole powinno zawsze znajdować się w sortowaniu [master]bazy danych, które ponownie będzie pasować do sortowania na serwerze.

ALE następujące pola sys.databasespochodzą z [name]pola w [mssqlsystemresource].[sys].[syspalvalues]:

• user_access_desc
• snapshot_isolation_state_desc
• recovery_model_desc
• page_verify_option_desc
• log_reuse_wait_desc
• zawiera_desc

Te pola powinny zawsze mieć zestawienie Latin1_General_CI_AS_KS_WS.

collation_namePole, jednak pochodzi z następującego wzoru:

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)

W tym miejscu zaczyna się pojawiać pierwszeństwo sortowania . Obie opcje wyjścia tutaj to funkcje systemowe: serverproperty()i collationpropertyfromid(). Zestawienie tego wyrażenia uważa się za „Coercible-default”:

Dowolna zmienna łańcucha znaków Transact-SQL, parametr, literał lub wynik wbudowanej funkcji katalogu lub wbudowanej funkcji, która nie pobiera danych wejściowych ciągu znaków, ale generuje dane wyjściowe ciągu znaków.

Jeśli obiekt zostanie zadeklarowany w funkcji zdefiniowanej przez użytkownika, procedurze składowanej lub wyzwalaczu, obiektowi zostanie przypisane domyślne sortowanie bazy danych, w której tworzona jest funkcja, procedura składowana lub wyzwalacz. Jeśli obiekt zostanie zadeklarowany wsadowo, obiektowi zostanie przypisane domyślne sortowanie bieżącej bazy danych dla połączenia.

W świetle tego drugiego akapitu, ponieważ sys.databasesjest to widok istniejący w masterbazie danych, przyjmuje sortowanie masterbazy danych (nie bieżącej bazy danych).

state_descPole jest również wyrazem:

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

Ale zestawienie tego wyrażenia jest Latin1_General_CI_AS_KS_WS. Czemu? Cóż, w tym wyrażeniu wprowadzono coś nowego: odniesienie do prawdziwego pola: [mssqlsystemresource].[sys].[syspalvalues].[name]w tej ostatniej ELSEklauzuli. Odniesienia do kolumn są uważane za „niejawne”:

Odwołanie do kolumny. Sortowanie wyrażenia jest pobierane z sortowania zdefiniowanego dla kolumny w tabeli lub widoku.

To oczywiście otwiera interesujące pytanie: czy to wyrażenie może zwrócić inne zestawienie, w zależności od tego, jak CASEjest ono oceniane? Literały będą znajdować się w sortowaniu bazy danych, w której ten obiekt jest zdefiniowany, ale ELSEwarunek zwraca wartość pola, która powinna zachować oryginalne sortowanie. Na szczęście możemy symulować test za pomocą funkcji dynamicznego zarządzania sys.dm_exec_describe_first_result_set :

-- 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

Zwraca (w instancji skonfigurowanej z zestawieniem, SQL_Latin1_General_CP1_CI_ASale działającym w bazie danych z zestawieniem 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

Widzimy tutaj, że dwa zapytania odwołujące się do pola [msdb]biorą pod uwagę sortowanie [msdb]bazy danych (która, jako systemowa baza danych, została określona przez sortowanie na serwerze).

Wracając do pierwotnego pytania

Obserwowane zachowanie polega na tym, że następujące pola nie obserwują ani sortowania serwera, ani sortowania bazy danych; zawsze są prezentowane w Latin1_General_CI_AS_KS_WSzestawieniu.

Twoja obserwacja jest natychmiastowa: pola te zawsze mają zestawienie Latin1_General_CI_AS_KS_WS, niezależnie od zestawienia serwera lub zestawienia bazy danych. A powodem jest: Priorytet sortowania. Te pola pochodzą z tabeli w [mssqlsystemresource]bazie danych i zachowają to wstępne sortowanie, chyba że zostaną zastąpione wyraźną COLLATEklauzulą, ponieważ ma to najwyższy priorytet:

Explicit = Wyrażenie, które jest jawnie rzutowane na określone zestawienie przy użyciu klauzuli COLLATE w wyrażeniu.

Jawne ma pierwszeństwo przed niejawnymi. Implicit ma pierwszeństwo przed Coercible-default:
Explicit> Implicit> Coercible-default

I powiązane pytanie:

Dlaczego zestawianie tych kolumn jest ustawione statycznie?

Nie chodzi o to, że są one ustawione statycznie, ani że inne pola są w jakiś sposób dynamiczne. Wszystkie pola we wszystkich widokach katalogu systemowego działają na tych samych zasadach pierwszeństwa sortowania. Powodem, dla którego wydają się być bardziej „statyczne” niż inne pola (tj. Nie zmieniają się, nawet jeśli zainstalujesz SQL Server z innym domyślnym zestawieniem, który z kolei tworzy systemowe bazy danych z tym domyślnym zestawieniem), jest to, że [mssqlsystemresource]baza danych konsekwentnie ma zestawienie Latin1_General_CI_AS_KS_WSdowolnej instalacji SQL Server (a przynajmniej tak się wydaje). Ma to sens, ponieważ w przeciwnym razie SQL Server miałby trudności z wewnętrznym zarządzaniem (tj. Gdyby zasady sortowania i porównania stosowane w wewnętrznej logice zmieniły się w zależności od instalacji).

Jak samemu zobaczyć te szczegóły

Jeśli chcesz zobaczyć źródło dowolnego pola (pól) w dowolnym z tych widoków katalogu systemowego, wykonaj następujące czynności:

1. Na karcie zapytania w SSMS włącz opcję zapytania „Uwzględnij rzeczywisty plan wykonania” ( CTRL-M)

2. Wykonaj zapytanie, wybierając jedno pole z jednego z widoków katalogu systemowego (zalecam wybieranie tylko jednego pola na raz, ponieważ plan wykonania jest absurdalnie duży / złożony nawet dla jednego pola i będzie zawierał odniesienia do wielu pól, których nie jesteś ” t wybór):

   SELECT recovery_model_desc FROM sys.databases;

3. Przejdź do zakładki „Plan wykonania”
4. Kliknij prawym przyciskiem myszy w graficznym obszarze Plan wykonania i wybierz „Pokaż XML planu wykonania ...”
5. Zostanie otwarta nowa karta w SSMS o tytule podobnym do: Execution plan.xml
6. Idź do Execution plan.xmlzakładki
7. Poszukaj pierwszego wystąpienia <OutputList>znacznika (zwykle powinno to być między wierszami 10 i 20)
8. Powinien być <ColumnReference>tag. Atrybuty tego znacznika powinny wskazywać określone pole w tabeli lub wskazywać wyrażenie zdefiniowane w dalszej części planu.

9. Jeśli atrybuty wskazują na rzeczywiste pole, oznacza to, że są gotowe, ponieważ zawierają wszystkie informacje. Oto, co pokazuje recovery_model_descpole:

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

10. Jeśli atrybuty wskazują na wyrażenie, na przykład jeśli zamiast tego zaznaczyłeś state_descpole, początkowo znajdziesz:

    <ColumnReference Column="Expr1024" />

11. W takim przypadku musisz przejrzeć resztę planu, aby znaleźć definicję Expr1024lub cokolwiek #, co ona wymyśli. Pamiętaj tylko, że może istnieć kilka takich odniesień, ale definicja nie będzie w <OutputList>bloku. Będzie jednak miał <ScalarOperator>element siostrzany, który zawiera definicję. Oto, co pokazuje state_descpole:

    <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">

To samo można zrobić, aby sprawdzić również źródło widoków katalogu na poziomie bazy danych. Wykonanie tego dla obiektu typu sys.tablespokaże, że namepole pochodzi od [current_db].[sys].[sysschobjs](dlatego ma sortowanie pasujące do sortowania w bazie danych), podczas gdy lock_escalation_descpole pochodzi od [mssqlsystemresource].[sys].[syspalvalues](i dlatego ma sortowanie Latin1_General_CI_AS_KS_WS).

Clippy mówi: „Wygląda na to, że chcesz wykonać zapytanie UNPIVOT”.

Teraz, gdy wiemy, czym jest Priorytet sortowania i jak działa, zastosujmy tę wiedzę do zapytania UNPIVOT.

W przypadku UNPIVOToperacji program SQL Server wydaje się naprawdę preferować (co oznacza: wymaga), aby każde pole źródłowe było tego samego typu . Zwykle „typ” odnosi się do typu podstawowego (tj. VARCHAR/ NVARCHAR/ INT/ Itp.), Ale tutaj SQL Server zawiera również COLLATION. Nie powinno to być postrzegane jako nierozsądne, biorąc pod uwagę kontrolę Collations: zestaw znaków (tj. Stronę kodową) dla VARCHAR oraz reguły językowe, które określają równoważność znaków i kombinacje znaków (tj. Normalizację). Poniżej znajduje się mimi-primer na temat tego, czym jest „normalizacja” Unicode:

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 '---';

Zwroty:

---
Danish_Greenlandic_100_CI_AI
---
Upper_Sorbian_100_CI_AI
---

A teraz zacznijmy oryginalne zapytanie. Zrobimy kilka testów, aby zobaczyć, jak różne zmiany wpływają na wynik, a następnie zobaczymy, jak tylko kilka zmian może to naprawić.

1. Pierwszy błąd dotyczy CompatibilityLevelpola, które jest drugim polem, które ma być niepodzielne, a akurat jest wyrażeniem zawierającym wszystkie literały łańcuchowe. Bez odniesień do pola wynikowe sortowanie uważa się za „coercible-default”). Domyślne ustawienia koercji przyjmują sortowanie bieżącej bazy danych, powiedzmy SQL_Latin1_General_CP1_CI_AS. Kolejne około 20 pól to również literały łańcuchowe, a zatem są również domyślnymi ustawieniami koercyjnymi, więc nie powinny być w konflikcie. Ale jeśli spojrzymy wstecz na pierwsze pole, recovery_model_descktóre pochodzi bezpośrednio z pola, w sys.databasesktórym jest to sortowanie „niejawne”, a to nie przyjmuje sortowania lokalnej bazy danych, ale zachowuje oryginalne sortowanie, którym jest Latin1_General_CI_AS_KS_WS( ponieważ tak naprawdę pochodzi od [mssqlsystemresource]DB).

   Tak więc, jeśli pole 1 (RecoveryModel) jest Latin1_General_CI_AS_KS_WS, a pole 2 (CompatibilityLevel) SQL_Latin1_General_CP1_CI_AS, to powinniśmy być w stanie wymusić Latin1_General_CI_AS_KS_WSdopasowanie pola 2 do pola 1, a następnie powinien pojawić się błąd dla pola 3 (AutoClose).

   Dodaj następujące na końcu CompatibilityLevellinii:
   COLLATE Latin1_General_CI_AS_KS_WS

   A następnie uruchom zapytanie. Oczywiście błąd mówi teraz, że to AutoClosepole jest w konflikcie.

2. W drugim teście musimy cofnąć wprowadzoną właśnie zmianę (tj. Usunąć COLLATEklauzulę z końca CompatibilityLevelwiersza).

   Teraz, jeśli SQL Server naprawdę ocenia w kolejności, w której pola są określone, powinniśmy być w stanie usunąć pole 1 (RecoveryModel), co spowoduje, że bieżące pole 2 (CompatibilityLevel) będzie polem, które ustawia sortowanie główne wynikowy UNPIVOT. A CompatibilityLevelpole jest coercible-default która odbywa się na sortowanie bazy danych, więc pierwszy błąd powinno być PageVerifypole, które jest nawiązaniem pole, które jest niejawna sortowania zachowując oryginalny sortowania, która w tym przypadku jest Latin1_General_CI_AS_KS_WSa co nie jest zestawienie aktualnej bazy danych.

   Więc idź dalej i skomentuj wiersz rozpoczynający się , RecoveryModelod SELECT(w górę), a następnie skomentuj RecoveryModelwiersz w UNPIVOTklauzuli poniżej i usuń przecinek wiodący z następnego wiersza CompatibilityLevel, aby uniknąć błędu składniowego.

   Uruchom to zapytanie. Oczywiście błąd mówi teraz, że to PageVerifypole jest w konflikcie.

3. W naszym trzecim teście musimy cofnąć zmiany, które właśnie wprowadziliśmy, aby usunąć RecoveryModelpole. Więc idź dalej i odłóż przecinek i odkomentuj te dwie pozostałe linie.

   Teraz możemy pójść w innym kierunku, wymuszając sortowanie. Zamiast zmieniać zestawianie domyślnych pól zestawiania koercyjnych (co jest ich większość), powinniśmy być w stanie zmienić ukryte pola zestawiania na obecne DB, prawda?

   Tak więc, podobnie jak nasz pierwszy test, powinniśmy być w stanie wymusić sortowanie pola 1 (RecoveryModel) za pomocą wyraźnej COLLATEklauzuli. Ale jeśli określimy określone sortowanie, a następnie uruchomimy zapytanie w bazie danych z innym sortowaniem, pola koagulacji domyślnej będą zbierać nowe sortowanie, które następnie będzie kolidować z tym, dla czego ustawiamy to pierwsze pole. To wydaje się być bólem. Na szczęście istnieje dynamiczny sposób, aby sobie z tym poradzić. Istnieje pseudo-sortowanie, DATABASE_DEFAULTktóre pobiera bieżące sortowanie baz danych (podobnie jak pola koercyjne-domyślne).

   Śmiało i dodaj na końcu wiersza, w kierunku do góry, który zaczyna się od , RecoveryModel: COLLATE DATABASE_DEFAULT

   Uruchom to zapytanie. Oczywiście błąd ponownie stwierdza, że PageVerifykonflikt jest polem.

4. W końcowym teście nie musimy cofać żadnych wcześniejszych zmian.

   Wszystko, co musimy teraz zrobić, aby naprawić to UNPIVOTzapytanie, to dodać COLLATE DATABASE_DEFAULTna końcu pozostałych ukrytych pól zestawienia: PageVerifyi RestrictedAccess. Chociaż Collationpole jest również niejawnym zestawieniem, pole to pochodzi z masterbazy danych, która zazwyczaj jest zgodna z „bieżącą” bazą danych. Ale jeśli chcesz być bezpieczny, aby to zawsze działało, dodaj COLLATE DATABASE_DEFAULTrównież koniec tego pola.

   Uruchom to zapytanie. Rzeczywiście, żadnych błędów. Aby naprawić to zapytanie, COLLATE DATABASE_DEFAULTwystarczyło dodać na końcu 3 pól (wymagane) i może jeszcze 1 (opcjonalnie).

5. Opcjonalnie Test: Teraz wreszcie mamy zapytanie UNPIVOT działa poprawnie, tylko jedna zmiana którejkolwiek z definicji pól poczynając CONVERT(VARCHAR(50),zamiast być 51, na przykład: CONVERT(VARCHAR(51),.

   Uruchom zapytanie. Powinieneś otrzymać ten sam The type of column "X" conflicts with the type of other columns specified in the UNPIVOT list.błąd, który wystąpił, gdy było to tylko niepasujące zestawienie.

   Otrzymanie tego samego błędu zarówno w przypadku niezgodności typu danych, jak i zestawień nie jest wystarczająco szczegółowe, aby było naprawdę pomocne. Jest więc zdecydowanie miejsce na ulepszenia :).

Uwaga związana z zapytaniem bardziej niż z konkretnym pytaniem dotyczącym sortowania:

Ponieważ wszystkie pola źródłowe są typu danych NVARCHAR, bezpieczniej byłoby dla CONVERTwszystkich pól wyjściowych NVARCHARzamiast VARCHAR. Być może w danym momencie nie masz do czynienia z danymi, które mają niestandardowe znaki ASCII, ale metadane systemowe pozwalają na ich konwersję do NVARCHAR(128)- co jest największą maksymalną długością dowolnego z tych pól - przynajmniej gwarantuje, że nie będzie problemu dla ciebie w przyszłości ani dla kogokolwiek kopiującego ten kod, który może już mieć niektóre z tych znaków w swoim systemie.

Jest to obejście konkretnego problemu, a nie pełna odpowiedź na pytanie. Można uniknąć błędów przy konwersji do sql_variantraczej niż 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;

Dodałem trzy kolumny, aby uzyskać informacje o podstawowym typie OptionValuekolumny.

Jeśli klient nie może obsłużyć sql_variantdanych, wykonaj końcową (najwyższy poziom) konwersję w unpvt.OptionValuekolumnie, np nvarchar(256).

Ok, więc rzuciłem okiem

sp_helptext [sys.databases]

potem zepsuł się, skąd dochodziły kolumny. Wszystkie z Latin1_General_CI_AS_KS_WSzestawieniem pochodzą z tabeli systemowej, sys.syspalvaluesktóra wydaje się być ogólną tabelą odnośników (jest to tabela systemowa, więc aby ją zobaczyć, będziesz musiał połączyć się przez DAC).

Domyślam się, że jest ustawiony Latin1_General_CI_AS_KS_WSna obsługę wszelkich możliwych wartości wyszukiwania. Widzę jednak, jak by to było denerwujące.

Innym sposobem zobaczenia definicji (pierwotnie podanej przez Maxa w komentarzu) jest:

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';`