SQL Server v.Next (Denali) : New, Changed and Removed System Objects

AaronBertrand — Mon, 29 Nov 2010 14:10:00 GMT

While it's still only the CTP1 timeframe, getting a glimpse of the changes in system objects can start preparing you for what's coming - especially when there are potential breaking changes, as you'll see below. Sure, a few of these things can change (and hopefully be fixed!) as the product gets closer to release, but once you have the scripts on disk, you can take a pulse at any time. Obviously, queries that go after the system objects directly are going to be much less tedious and annoying than trying to parse Books Online for changes (even if you assume that Books Online is up to date - which, as you'll see below, is not the case).

Before starting, I'll assume that you have a 2008 R2 instance and a Denali instance; these instances should have valid linked servers pointing at each other. In the code below you will see references to fairly intuitive linked server names: [GREENLANTERN\SQL2008R2] and [GREENLANTERN\DENALI]. I will try to remember to make it clear which instance the code should run from (in a few cases it matters, because functions like OBJECT_SCHEMA_NAME() don't work remotely without using <linked server>...sp_executeSQL).

New system objects in SQL Server "Denali"

This is a query run from the Denali server that will find objects that did not exist in 2008 R2. I had to code in an exclusion in the where clause; while sp_MS_marksystemobject certainly exists in 2008 R2, it does not appear in sys.all_objects the way it does in Denali.

SELECT
    [name] = N'sys.' + o.name,
    [type] = o.type_desc
FROM
    [master].sys.all_objects AS o
LEFT OUTER JOIN
    [GREENLANTERN\SQL2008R2].[master].sys.all_objects AS oo
ON
    o.name = oo.name
    AND o.[schema_id] = oo.[schema_id]
WHERE
    oo.name IS NULL
    AND o.[schema_id] = 4
    AND o.name <> N'sp_MS_marksystemobject'
ORDER BY
    o.type_desc,
    o.name;

Some of these objects are documented, but several still are not listed in the current version of Denali BOL. I was going to remove the links for the items that aren't documented, but I'll leave all of the search links intact so that they will continue to work when they *are* added to Books Online.

name type

sys.sp_availability_group_command_internal EXTENDED_STORED_PROCEDURE

sys.sp_describe_first_result_set EXTENDED_STORED_PROCEDURE

sys.sp_describe_undeclared_parameters EXTENDED_STORED_PROCEDURE

sys.sp_migrate_user_to_contained EXTENDED_STORED_PROCEDURE

sys.sp_server_diagnostics EXTENDED_STORED_PROCEDURE

sys.dm_db_objects_disabled_on_compatibility_level_change SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_exec_describe_first_result_set SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_exec_describe_first_result_set_for_object SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_fts_index_keywords_by_property SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logconsumer_cachebufferrefs SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logconsumer_privatecachebuffers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpool_consumers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpool_sharedcachebuffers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_freepools SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_respoolsize SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_stats SQL_INLINE_TABLE_VALUED_FUNCTION

sys.sp_filestream_force_garbage_collection SQL_STORED_PROCEDURE

sys.sp_filestream_recalculate_container_size SQL_STORED_PROCEDURE

sys.sp_help_spatial_geography_histogram SQL_STORED_PROCEDURE

sys.sp_help_spatial_geometry_histogram SQL_STORED_PROCEDURE

sys.sp_sequence_get_range SQL_STORED_PROCEDURE

sys.sysclones SYSTEM_TABLE

sys.sysdbfiles SYSTEM_TABLE

sys.sysftproperties SYSTEM_TABLE

sys.sysmatrixages SYSTEM_TABLE

sys.sysmatrixbricks SYSTEM_TABLE

sys.sysmatrixconfig SYSTEM_TABLE

sys.sysmatrixmanagers SYSTEM_TABLE

sys.sysxmitbody SYSTEM_TABLE

sys.availability_groups VIEW

sys.availability_replicas VIEW

sys.dm_db_uncontained_entities VIEW

sys.dm_hadr_availability_group_states VIEW

sys.dm_hadr_availability_replica_states VIEW

sys.dm_hadr_database_replica_states VIEW

sys.dm_hadr_database_synchronization_states VIEW

sys.dm_hadr_instance_node_map VIEW

sys.dm_hadr_name_id_map VIEW

sys.dm_logpool_hashentries VIEW

sys.dm_logpool_stats VIEW

sys.registered_search_properties VIEW

sys.registered_search_property_lists VIEW

sys.sequences VIEW

name	type
sys.sp_availability_group_command_internal	EXTENDED_STORED_PROCEDURE
sys.sp_describe_first_result_set	EXTENDED_STORED_PROCEDURE
sys.sp_describe_undeclared_parameters	EXTENDED_STORED_PROCEDURE
sys.sp_migrate_user_to_contained	EXTENDED_STORED_PROCEDURE
sys.sp_server_diagnostics	EXTENDED_STORED_PROCEDURE
sys.dm_db_objects_disabled_on_compatibility_level_change	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_index_keywords_by_property	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_cachebufferrefs	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_sharedcachebuffers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_freepools	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.sp_filestream_force_garbage_collection	SQL_STORED_PROCEDURE
sys.sp_filestream_recalculate_container_size	SQL_STORED_PROCEDURE
sys.sp_help_spatial_geography_histogram	SQL_STORED_PROCEDURE
sys.sp_help_spatial_geometry_histogram	SQL_STORED_PROCEDURE
sys.sp_sequence_get_range	SQL_STORED_PROCEDURE
sys.sysclones	SYSTEM_TABLE
sys.sysdbfiles	SYSTEM_TABLE
sys.sysftproperties	SYSTEM_TABLE
sys.sysmatrixages	SYSTEM_TABLE
sys.sysmatrixbricks	SYSTEM_TABLE
sys.sysmatrixconfig	SYSTEM_TABLE
sys.sysmatrixmanagers	SYSTEM_TABLE
sys.sysxmitbody	SYSTEM_TABLE
sys.availability_groups	VIEW
sys.availability_replicas	VIEW
sys.dm_db_uncontained_entities	VIEW
sys.dm_hadr_availability_group_states	VIEW
sys.dm_hadr_availability_replica_states	VIEW
sys.dm_hadr_database_replica_states	VIEW
sys.dm_hadr_database_synchronization_states	VIEW
sys.dm_hadr_instance_node_map	VIEW
sys.dm_hadr_name_id_map	VIEW
sys.dm_logpool_hashentries	VIEW
sys.dm_logpool_stats	VIEW
sys.registered_search_properties	VIEW
sys.registered_search_property_lists	VIEW
sys.sequences	VIEW

System objects dropped in SQL Server "Denali"

We can run a query similar to the above from the 2008 R2 instance, to see what kind of things will no longer work in Denali.

SELECT
    [name] = N'sys.' + o.name,
    [type] = o.type_desc
FROM
    [master].sys.all_objects AS o
LEFT OUTER JOIN
    [GREENLANTERN\DENALI].[master].sys.all_objects AS oo
ON
    o.name = oo.name
    AND o.[schema_id] = oo.[schema_id]
WHERE
    oo.name IS NULL
    AND o.[schema_id] = 4
ORDER BY
    o.type_desc,
    o.name;

The results: these are mostly XPs that have been marked for deprecation several versions ago (and actually are still marked as "future deprecated" in the current Denali Books Online - see Connect #625200) However I cannot yet explain the service broker DMV that is missing; I filed a bug about this in Connect #625206.

name type

sys.sp_batch_params EXTENDED_STORED_PROCEDURE

sys.sp_fetchLOBfromcookie EXTENDED_STORED_PROCEDURE

sys.xp_adsirequest EXTENDED_STORED_PROCEDURE

sys.xp_deletemail EXTENDED_STORED_PROCEDURE

sys.xp_findnextmsg EXTENDED_STORED_PROCEDURE

sys.xp_get_mapi_default_profile EXTENDED_STORED_PROCEDURE

sys.xp_get_mapi_profiles EXTENDED_STORED_PROCEDURE

sys.xp_MSADEnabled EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIObjReg EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIObjRegDB EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIReg EXTENDED_STORED_PROCEDURE

sys.xp_readmail EXTENDED_STORED_PROCEDURE

sys.xp_sendmail EXTENDED_STORED_PROCEDURE

sys.xp_startmail EXTENDED_STORED_PROCEDURE

sys.xp_stopmail EXTENDED_STORED_PROCEDURE

sys.xp_test_mapi_profile EXTENDED_STORED_PROCEDURE

sys.sp_ActiveDirectory_Obj SQL_STORED_PROCEDURE

sys.sp_ActiveDirectory_SCP SQL_STORED_PROCEDURE

sys.sp_ActiveDirectory_Start SQL_STORED_PROCEDURE

sys.sp_dropalias SQL_STORED_PROCEDURE

sys.sp_MScheckIsPubOfSub SQL_STORED_PROCEDURE

sys.sp_processmail SQL_STORED_PROCEDURE

sys.database_principal_aliases VIEW

sys.dm_broker_forwarded_messages VIEW

name	type
sys.sp_batch_params	EXTENDED_STORED_PROCEDURE
sys.sp_fetchLOBfromcookie	EXTENDED_STORED_PROCEDURE
sys.xp_adsirequest	EXTENDED_STORED_PROCEDURE
sys.xp_deletemail	EXTENDED_STORED_PROCEDURE
sys.xp_findnextmsg	EXTENDED_STORED_PROCEDURE
sys.xp_get_mapi_default_profile	EXTENDED_STORED_PROCEDURE
sys.xp_get_mapi_profiles	EXTENDED_STORED_PROCEDURE
sys.xp_MSADEnabled	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIObjReg	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIObjRegDB	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIReg	EXTENDED_STORED_PROCEDURE
sys.xp_readmail	EXTENDED_STORED_PROCEDURE
sys.xp_sendmail	EXTENDED_STORED_PROCEDURE
sys.xp_startmail	EXTENDED_STORED_PROCEDURE
sys.xp_stopmail	EXTENDED_STORED_PROCEDURE
sys.xp_test_mapi_profile	EXTENDED_STORED_PROCEDURE
sys.sp_ActiveDirectory_Obj	SQL_STORED_PROCEDURE
sys.sp_ActiveDirectory_SCP	SQL_STORED_PROCEDURE
sys.sp_ActiveDirectory_Start	SQL_STORED_PROCEDURE
sys.sp_dropalias	SQL_STORED_PROCEDURE
sys.sp_MScheckIsPubOfSub	SQL_STORED_PROCEDURE
sys.sp_processmail	SQL_STORED_PROCEDURE
sys.database_principal_aliases	VIEW
sys.dm_broker_forwarded_messages	VIEW

DMV / Catalog View columns added or changed in SQL Server "Denali"

This query, run on the Denali instance, will show new or changed columns compared to SQL Server 2008 R2:

;WITH r AS
(
    SELECT
        c.[object_id],
        [schema]      = OBJECT_SCHEMA_NAME(c.[object_id]),
        viewname      = v.name,
        col           = c.name,
        t             = c.system_type_id,
        [type]        = t.name,
        c.[precision],
        c.scale,
        c.max_length
    FROM
        [master].sys.all_columns AS c
    INNER JOIN
        [master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
    INNER JOIN
        [master].sys.types AS t
        ON t.system_type_id = c.system_type_id
    WHERE
        t.name <> N'sysname'
)
SELECT
    [status] = CASE
        WHEN l.viewname IS NULL THEN 'new'
        ELSE 'changed'
    END,
    r.[schema],
    r.viewname,
    r.col,
    r.[type],
    FormerType = l.[type],
    r.[precision],
    FormerPrecision = l.[precision],
    r.scale,
    FormerScale = l.scale,
    max_length = r.max_length / CASE 
        WHEN r.[type] = N'nvarchar' THEN 2 ELSE 1 END,
    FormerMaxLength = l.max_length / CASE 
        WHEN l.[type] = N'nvarchar' THEN 2 ELSE 1 END
FROM
    r
LEFT OUTER JOIN
(
    SELECT
        [schema] = s.name,
        viewname = v.name,
        col = c.name,
        t = c.system_type_id,
        [type] = t.name,
        c.[precision],
        c.scale,
        c.max_length
    FROM
        [GREENLANTERN\SQL2008R2].[master].sys.all_columns AS c
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.schemas AS s
        ON v.[schema_id] = s.[schema_id]
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.types AS t
        ON c.system_type_id = t.system_type_id
    WHERE
        t.name <> N'sysname'
) AS l
ON
    r.viewname = l.viewname
    AND r.[schema] = l.[schema]
    AND r.col = l.col
WHERE
    (
        l.col IS NULL
        OR
        (
            r.t <> COALESCE(l.t, -10)
            OR r.[precision] <> COALESCE(l.[precision], -10)
            OR r.scale <> COALESCE(l.scale, -10)
            OR r.max_length <> COALESCE(l.max_length, -10)
        )
    )
    AND EXISTS
    (
        SELECT 1
            FROM [GREENLANTERN\SQL2008R2].[master].sys.all_views
            WHERE name = r.viewname
    )
ORDER BY
   [status] DESC,
   r.viewname,
   r.col;

The results: many new property columns for sys.databases, several new (well, renamed; see below) memory usage indicators in a few key DMVs, and some new columns around contained databases. (Please note that not all of the columns should imply that those features, e.g. two-digit year cutoff, will be supported in the next major version of SQL Server.) Also, a handful of the big string columns in various DMVs have been widened from 256 or 2,048 characters to 3,072; I'm not sure if any of these caused bugs in earlier versions, or they are just being proactive.

column status view name column name new data type old data type

new sys.all_sql_modules is_contained bit -

new sys.data_spaces is_system bit -

new sys.database_principals authentication_type int -

new sys.database_principals authentication_type_desc nvarchar(60) -

new sys.database_principals default_language_lcid int -

new sys.database_principals default_language_name nvarchar(128) -

new sys.databases containment tinyint -

new sys.databases containment_desc nvarchar(60) -

new sys.databases default_fulltext_language_lcid int -

new sys.databases default_fulltext_language_name nvarchar(128) -

new sys.databases default_language_lcid smallint -

new sys.databases default_language_name nvarchar(128) -

new sys.databases group_database_id uniqueidentifier -

new sys.databases is_nested_triggers_on bit -

new sys.databases is_transform_noise_words_on bit -

new sys.databases replica_id uniqueidentifier -

new sys.databases two_digit_year_cutoff smallint -

new sys.dm_clr_appdomains compatibility_level int -

new sys.dm_database_encryption_keys encryptor_type nvarchar(128) -

new sys.dm_db_file_space_usage allocated_extent_page_count bigint -

new sys.dm_db_file_space_usage filegroup_id smallint -

new sys.dm_db_file_space_usage total_page_count bigint -

new sys.dm_exec_cached_plans global_token varbinary(14) -

new sys.dm_exec_sessions authenticating_database_id int -

new sys.dm_os_memory_cache_counters pages_in_use_kb bigint -

new sys.dm_os_memory_cache_counters pages_kb bigint -

new sys.dm_os_memory_cache_entries pages_kb bigint -

new sys.dm_os_memory_clerks page_size_in_bytes bigint -

new sys.dm_os_memory_clerks pages_kb bigint -

new sys.dm_os_memory_nodes foreign_committed_kb bigint -

new sys.dm_os_memory_nodes pages_kb bigint -

new sys.dm_os_memory_objects max_pages_in_bytes bigint -

new sys.dm_os_memory_objects pages_in_bytes bigint -

new sys.dm_os_sys_info committed_kb bigint -

new sys.dm_os_sys_info committed_target_kb bigint -

new sys.dm_os_sys_info physical_memory_kb bigint -

new sys.dm_os_sys_info virtual_machine_type int -

new sys.dm_os_sys_info virtual_machine_type_desc nvarchar(60) -

new sys.dm_os_sys_info virtual_memory_kb bigint -

new sys.dm_os_sys_info visible_target_kb bigint -

new sys.dm_os_worker_local_storage query_driver_address varbinary(8) -

new sys.filegroups is_system bit -

new sys.fulltext_indexes property_list_id int -

new sys.partition_functions is_system bit -

new sys.partition_schemes is_system bit -

new sys.server_event_session_events predicate_xml nvarchar(0) -

new sys.server_principals is_fixed_role bit -

new sys.server_principals owning_principal_id int -

new sys.server_trigger_events is_trigger_event bit -

new sys.sql_modules is_contained bit -

new sys.trigger_events is_trigger_event bit -

changed sys.dm_os_memory_cache_entries entry_data nvarchar(3072) nvarchar(2048)

changed sys.dm_os_ring_buffers record nvarchar(3072) nvarchar(2048)

changed sys.dm_os_waiting_tasks resource_description nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_map_values map_value nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_object_columns description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_objects description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_packages description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_session_events event_predicate nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_session_object_columns column_value nvarchar(3072) nvarchar(2048)

changed sys.syscacheobjects cacheobjtype nvarchar(50) nvarchar(17)

column status	view name	column name	new data type	old data type
new	sys.all_sql_modules	is_contained	bit	-
new	sys.data_spaces	is_system	bit	-
new	sys.database_principals	authentication_type	int	-
new	sys.database_principals	authentication_type_desc	nvarchar(60)	-
new	sys.database_principals	default_language_lcid	int	-
new	sys.database_principals	default_language_name	nvarchar(128)	-
new	sys.databases	containment	tinyint	-
new	sys.databases	containment_desc	nvarchar(60)	-
new	sys.databases	default_fulltext_language_lcid	int	-
new	sys.databases	default_fulltext_language_name	nvarchar(128)	-
new	sys.databases	default_language_lcid	smallint	-
new	sys.databases	default_language_name	nvarchar(128)	-
new	sys.databases	group_database_id	uniqueidentifier	-
new	sys.databases	is_nested_triggers_on	bit	-
new	sys.databases	is_transform_noise_words_on	bit	-
new	sys.databases	replica_id	uniqueidentifier	-
new	sys.databases	two_digit_year_cutoff	smallint	-
new	sys.dm_clr_appdomains	compatibility_level	int	-
new	sys.dm_database_encryption_keys	encryptor_type	nvarchar(128)	-
new	sys.dm_db_file_space_usage	allocated_extent_page_count	bigint	-
new	sys.dm_db_file_space_usage	filegroup_id	smallint	-
new	sys.dm_db_file_space_usage	total_page_count	bigint	-
new	sys.dm_exec_cached_plans	global_token	varbinary(14)	-
new	sys.dm_exec_sessions	authenticating_database_id	int	-
new	sys.dm_os_memory_cache_counters	pages_in_use_kb	bigint	-
new	sys.dm_os_memory_cache_counters	pages_kb	bigint	-
new	sys.dm_os_memory_cache_entries	pages_kb	bigint	-
new	sys.dm_os_memory_clerks	page_size_in_bytes	bigint	-
new	sys.dm_os_memory_clerks	pages_kb	bigint	-
new	sys.dm_os_memory_nodes	foreign_committed_kb	bigint	-
new	sys.dm_os_memory_nodes	pages_kb	bigint	-
new	sys.dm_os_memory_objects	max_pages_in_bytes	bigint	-
new	sys.dm_os_memory_objects	pages_in_bytes	bigint	-
new	sys.dm_os_sys_info	committed_kb	bigint	-
new	sys.dm_os_sys_info	committed_target_kb	bigint	-
new	sys.dm_os_sys_info	physical_memory_kb	bigint	-
new	sys.dm_os_sys_info	virtual_machine_type	int	-
new	sys.dm_os_sys_info	virtual_machine_type_desc	nvarchar(60)	-
new	sys.dm_os_sys_info	virtual_memory_kb	bigint	-
new	sys.dm_os_sys_info	visible_target_kb	bigint	-
new	sys.dm_os_worker_local_storage	query_driver_address	varbinary(8)	-
new	sys.filegroups	is_system	bit	-
new	sys.fulltext_indexes	property_list_id	int	-
new	sys.partition_functions	is_system	bit	-
new	sys.partition_schemes	is_system	bit	-
new	sys.server_event_session_events	predicate_xml	nvarchar(0)	-
new	sys.server_principals	is_fixed_role	bit	-
new	sys.server_principals	owning_principal_id	int	-
new	sys.server_trigger_events	is_trigger_event	bit	-
new	sys.sql_modules	is_contained	bit	-
new	sys.trigger_events	is_trigger_event	bit	-
changed	sys.dm_os_memory_cache_entries	entry_data	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_os_ring_buffers	record	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_os_waiting_tasks	resource_description	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_map_values	map_value	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_object_columns	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_objects	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_packages	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_session_events	event_predicate	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_session_object_columns	column_value	nvarchar(3072)	nvarchar(2048)
changed	sys.syscacheobjects	cacheobjtype	nvarchar(50)	nvarchar(17)

DMV / Catalog View columns dropped from SQL Server "Denali"

And finally, we should check for columns that have been dropped from DMVs or catalog views, since cpu_ticks_in_ms was dropped from sys.dm_os_sys_info in SQL Server 2008 with little fanfare. On the 2008 R2 instance, you can run this query:

;WITH r AS
(
    SELECT
        c.[object_id],
        [schema] = OBJECT_SCHEMA_NAME(c.[object_id]),
        viewname = v.name,
        col = c.name
    FROM
        [master].sys.all_columns AS c
    INNER JOIN
        [master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
)
SELECT
    r.[schema],
    r.viewname,
    r.col
FROM
    r
LEFT OUTER JOIN
(
    SELECT
        [schema] = s.name,
        viewname = v.name,
        col = c.name
    FROM
        [GREENLANTERN\DENALI].[master].sys.all_columns AS c
    INNER JOIN
        [GREENLANTERN\DENALI].[master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
    INNER JOIN
        [GREENLANTERN\DENALI].[master].sys.schemas AS s
        ON v.[schema_id] = s.[schema_id]
) AS l
ON
    r.viewname = l.viewname
    AND r.[schema] = l.[schema]
    AND r.col = l.col
WHERE
    l.col IS NULL
    AND EXISTS
    (
        SELECT 1
            FROM [GREENLANTERN\DENALI].[master].sys.all_views
            WHERE name = r.viewname
    )
ORDER BY
   r.viewname,
   r.col;

The results: as it turns out, the only columns that have been "dropped" are the ones that have been renamed or consolidated (they are listed as new above).

view name column name

sys.dm_os_memory_cache_counters multi_pages_in_use_kb

sys.dm_os_memory_cache_counters multi_pages_kb

sys.dm_os_memory_cache_counters single_pages_in_use_kb

sys.dm_os_memory_cache_counters single_pages_kb

sys.dm_os_memory_cache_entries pages_allocated_count

sys.dm_os_memory_clerks multi_pages_kb

sys.dm_os_memory_clerks page_size_bytes

sys.dm_os_memory_clerks single_pages_kb

sys.dm_os_memory_nodes multi_pages_kb

sys.dm_os_memory_nodes single_pages_kb

sys.dm_os_memory_objects max_pages_allocated_count

sys.dm_os_memory_objects pages_allocated_count

sys.dm_os_sys_info bpool_commit_target

sys.dm_os_sys_info bpool_committed

sys.dm_os_sys_info bpool_visible

sys.dm_os_sys_info physical_memory_in_bytes

sys.dm_os_sys_info virtual_memory_in_bytes

view name	column name
sys.dm_os_memory_cache_counters	multi_pages_in_use_kb
sys.dm_os_memory_cache_counters	multi_pages_kb
sys.dm_os_memory_cache_counters	single_pages_in_use_kb
sys.dm_os_memory_cache_counters	single_pages_kb
sys.dm_os_memory_cache_entries	pages_allocated_count
sys.dm_os_memory_clerks	multi_pages_kb
sys.dm_os_memory_clerks	page_size_bytes
sys.dm_os_memory_clerks	single_pages_kb
sys.dm_os_memory_nodes	multi_pages_kb
sys.dm_os_memory_nodes	single_pages_kb
sys.dm_os_memory_objects	max_pages_allocated_count
sys.dm_os_memory_objects	pages_allocated_count
sys.dm_os_sys_info	bpool_commit_target
sys.dm_os_sys_info	bpool_committed
sys.dm_os_sys_info	bpool_visible
sys.dm_os_sys_info	physical_memory_in_bytes
sys.dm_os_sys_info	virtual_memory_in_bytes

If you're currently using any of these DMVs in your own code, this is potentially a major breaking change - you may want to make note of the columns that have changed, as upgrading to Denali may break some of your scripts.

Search results matching tags 'denali', 'catalog views', 'SQL Server 11', and 'SQL 11'

SQL Server v.Next (Denali) : New, Changed and Removed System Objects