1

Each version of SQL Server potentially adds new columns to the DMVs. These new columns are available even if you change the compatibility level of the database to a previous version.

Is there a list somewhere that shows which columns are available for a given version of SQL Server?

  • 5
    The documentation, unfortunately. – Randolph West Jan 9 at 19:43
  • Or lean on Glenn Berry though you may be customizing some. – scsimon Jan 9 at 20:22
2

Each version of SQL Server potentially adds new columns to the DMVs.

Correct, and since each CU is technically a new version this can happen inside of major version releases.

Is there a list somewhere that shows which columns are available for a given version of SQL Server?

While this may not be in the spirit of the question asked, there technically is an answer which is to use the system tables. For example, what columns does sys.availability_groups currently have in this version (14.0.3037):

select name FROM sys.all_columns where object_id = object_id('sys.availability_groups') order by column_id asc

Will output:

╔═════════════════════════════════════════════╗
║                    name                     ║
╠═════════════════════════════════════════════╣
║ group_id                                    ║
║ name                                        ║
║ resource_id                                 ║
║ resource_group_id                           ║
║ failure_condition_level                     ║
║ health_check_timeout                        ║
║ automated_backup_preference                 ║
║ automated_backup_preference_desc            ║
║ version                                     ║
║ basic_features                              ║
║ dtc_support                                 ║
║ db_failover                                 ║
║ is_distributed                              ║
║ cluster_type                                ║
║ cluster_type_desc                           ║
║ required_synchronized_secondaries_to_commit ║
║ sequence_number                             ║
╚═════════════════════════════════════════════╝

More in the spirit of the question, Docs is your best source for columns based on version outside of your own queries.

2

One method to show the columns available in DMVs of different versions without consulting the documentation is with an SSMS multi-server query. Assuming you already have the versions installed, create an SSMS registered server group with a registered server for each version. Then, right-click on the group name and select new query to open a multi-server query window and run this script to list the version, patch level, and columns:

DECLARE @ObjectName nvarchar(261) = N'sys.availability_groups';
SELECT
      SERVERPROPERTY('ProductVersion') AS ProductVersion
    , SERVERPROPERTY('ProductLevel') AS ProductLevel
    , @ObjectName AS ObjectName
    , name AS ColumnName
FROM sys.all_columns
WHERE object_id = object_id(@ObjectName)
ORDER BY column_id ASC;

Below are example results from my test machine using the DMV in @SeanGallardy's answer, I'm a bit behind on patching these instances :-).

+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
|       Server Name        | ProductVersion | ProductLevel |       ObjectName        |                 ColumnName                  |
+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | group_id                                    |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | name                                        |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | resource_id                                 |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | resource_group_id                           |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | failure_condition_level                     |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | health_check_timeout                        |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | automated_backup_preference                 |
| WIN2016TEST1\SQL2012     |    11.0.7001.0 | SP4          | sys.availability_groups | automated_backup_preference_desc            |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | group_id                                    |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | name                                        |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | resource_id                                 |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | resource_group_id                           |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | failure_condition_level                     |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | health_check_timeout                        |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | automated_backup_preference                 |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | automated_backup_preference_desc            |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | version                                     |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | basic_features                              |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | dtc_support                                 |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | db_failover                                 |
| WIN2016TEST1\SQL2016     |    13.0.5233.0 | SP2          | sys.availability_groups | is_distributed                              |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | group_id                                    |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | name                                        |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | resource_id                                 |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | resource_group_id                           |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | failure_condition_level                     |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | health_check_timeout                        |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | automated_backup_preference                 |
| WIN2016TEST1\SQL2014     |    12.0.6024.0 | SP3          | sys.availability_groups | automated_backup_preference_desc            |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | group_id                                    |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | name                                        |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | resource_id                                 |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | resource_group_id                           |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | failure_condition_level                     |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | health_check_timeout                        |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | automated_backup_preference                 |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | automated_backup_preference_desc            |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | version                                     |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | basic_features                              |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | dtc_support                                 |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | db_failover                                 |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | is_distributed                              |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | cluster_type                                |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | cluster_type_desc                           |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | required_synchronized_secondaries_to_commit |
| WIN2016TEST1\SQL2017     |   14.0.3045.24 | RTM          | sys.availability_groups | sequence_number                             |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | group_id                                    |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | name                                        |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | resource_id                                 |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | resource_group_id                           |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | failure_condition_level                     |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | health_check_timeout                        |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | automated_backup_preference                 |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | automated_backup_preference_desc            |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | version                                     |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | basic_features                              |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | dtc_support                                 |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | db_failover                                 |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | is_distributed                              |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | cluster_type                                |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | cluster_type_desc                           |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | required_synchronized_secondaries_to_commit |
| WIN2016TEST1\SQL2019CTP2 |   15.0.1200.24 | CTP2.2       | sys.availability_groups | sequence_number                             |
+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
  • Interesting. Though, without having an instance for every possible service pack and CU for each version, it would not be possible to determine which of those introduced the new column. Sure, it is certainly infrequent to introduce new columns in SPs and CUs compared to full releases, so this will likely get close-enough, but it should be a clearly stated limitation, just to ensure not setting false expectations. – Solomon Rutzky Jan 10 at 14:31
1

The definitive answer is of course the documentation.

This isn't always easy to track down though - especially if you are attempting to write scripts compatible with older versions (such as 2005).

If you only want to cater for additional columns and you have a server version at the lower and upper end of the target range you can use a trick like this one to write a query compatible with all server versions in that range.

With OptionalColumns AS
(
--Added between 2005 and 2017
SELECT 
is_auto_create_stats_incremental_on = CAST(NULL AS bit),
is_query_store_on = CAST(NULL AS bit),
is_cdc_enabled = CAST(NULL AS bit),
is_encrypted = CAST(NULL AS bit),
is_honor_broker_priority_on = CAST(NULL AS bit),
replica_id = CAST(NULL AS uniqueidentifier),
group_database_id = CAST(NULL AS uniqueidentifier),
resource_pool_id = CAST(NULL AS int),
default_language_lcid = CAST(NULL AS smallint),
default_language_name = CAST(NULL AS nvarchar(128)),
default_fulltext_language_lcid = CAST(NULL AS int),
default_fulltext_language_name = CAST(NULL AS nvarchar(128)),
is_nested_triggers_on = CAST(NULL AS bit),
is_transform_noise_words_on = CAST(NULL AS bit),
two_digit_year_cutoff = CAST(NULL AS smallint),
containment = CAST(NULL AS tinyint),
containment_desc = CAST(NULL AS nvarchar(60)),
target_recovery_time_in_seconds = CAST(NULL AS int),
delayed_durability = CAST(NULL AS int),
delayed_durability_desc = CAST(NULL AS nvarchar(60)),
is_memory_optimized_elevate_to_snapshot_on = CAST(NULL AS bit),
is_federation_member = CAST(NULL AS bit),
is_remote_data_archive_enabled = CAST(NULL AS bit),
is_mixed_page_allocation_on = CAST(NULL AS bit),
is_temporal_history_retention_enabled = CAST(NULL AS bit)
), sys_databases AS
(
SELECT ca.*
FROM OptionalColumns
CROSS APPLY
(
--These will be resolved from sys.databases if they exist there in the current version or the OptionalColumns CTE otherwise
SELECT        name, database_id, source_database_id, owner_sid, create_date, compatibility_level, collation_name, user_access, user_access_desc, is_read_only, is_auto_close_on, is_auto_shrink_on, state, state_desc, is_in_standby, 
                         is_cleanly_shutdown, is_supplemental_logging_enabled, snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model, recovery_model_desc, page_verify_option, 
                         page_verify_option_desc, is_auto_create_stats_on, is_auto_create_stats_incremental_on, is_auto_update_stats_on, is_auto_update_stats_async_on, is_ansi_null_default_on, is_ansi_nulls_on, is_ansi_padding_on, 
                         is_ansi_warnings_on, is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on, is_quoted_identifier_on, is_recursive_triggers_on, is_cursor_close_on_commit_on, is_local_cursor_default, 
                         is_fulltext_enabled, is_trustworthy_on, is_db_chaining_on, is_parameterization_forced, is_master_key_encrypted_by_server, is_query_store_on, is_published, is_subscribed, is_merge_published, is_distributor, 
                         is_sync_with_backup, service_broker_guid, is_broker_enabled, log_reuse_wait, log_reuse_wait_desc, is_date_correlation_on, is_cdc_enabled, is_encrypted, is_honor_broker_priority_on, replica_id, group_database_id, 
                         resource_pool_id, default_language_lcid, default_language_name, default_fulltext_language_lcid, default_fulltext_language_name, is_nested_triggers_on, is_transform_noise_words_on, two_digit_year_cutoff, containment, 
                         containment_desc, target_recovery_time_in_seconds, delayed_durability, delayed_durability_desc, is_memory_optimized_elevate_to_snapshot_on, is_federation_member, is_remote_data_archive_enabled, 
                         is_mixed_page_allocation_on, is_temporal_history_retention_enabled
FROM            sys.databases
) ca
)
SELECT *
FROM sys_databases

In the absence of information about columns available in 2005 the same technique could be used but just enumerating all the columns in the OptionalColumns CTE rather than being able to target just the newly added ones.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.