Hi Friends,

In this post I want to show you how you can find out SQL Server 2014 New System Objects, yourself!

From SQL Server 2014 VM (or a physical box), add a Linked Server connection to a SQL Server 2012 VM (or a physical box)

And execute the following script:

SELECT a.NAME sql14_objname
    ,a.xtype sql14_objtype
    ,b.NAME
FROM sys.sysobjects a
LEFT JOIN [WIN2K8R2\SQL2012SP1].master.sys.sysobjects b ON a.NAME = b.NAME
WHERE b.NAME IS NULL
ORDER BY sql14_objtype

And you get the output as follows:

sql14_objname sql14_objtype
fn_hadr_is_primary_replica FN NULL
fn_column_store_row_groups IF NULL
fn_dblog_xtp IF NULL
fn_dump_dblog_xtp IF NULL
dm_db_stats_properties_internal IF NULL
sqlagent_job_history IT NULL
sqlagent_jobs IT NULL
sqlagent_jobsteps IT NULL
sqlagent_jobsteps_logs IT NULL
sp_cci_tuple_mover P NULL
sp_sqlagent_add_job P NULL
sp_sqlagent_add_jobstep P NULL
sp_sqlagent_delete_job P NULL
sp_sqlagent_help_jobstep P NULL
sp_sqlagent_log_job_history P NULL
sp_sqlagent_start_job P NULL
sp_sqlagent_stop_job P NULL
sp_sqlagent_verify_database_context P NULL
sp_sqlagent_write_jobstep_log P NULL
sp_xtp_bind_db_resource_pool P NULL
sp_xtp_checkpoint_force_garbage_collection P NULL
sp_xtp_control_proc_exec_stats P NULL
sp_xtp_control_query_exec_stats P NULL
sp_xtp_merge_checkpoint_files P NULL
sp_xtp_unbind_db_resource_pool P NULL
syscsrowgroups S NULL
sysextfileformats S NULL
sysextsources S NULL
sysexttables S NULL
fn_sqlagent_job_history TF NULL
fn_sqlagent_jobs TF NULL
fn_sqlagent_jobsteps TF NULL
fn_sqlagent_jobsteps_logs TF NULL
fn_sqlagent_subsystems TF NULL
hash_indexes V NULL
dm_db_xtp_checkpoint_files V NULL
dm_db_xtp_checkpoint_stats V NULL
dm_db_xtp_gc_cycle_stats V NULL
dm_db_xtp_hash_index_stats V NULL
dm_db_xtp_index_stats V NULL
dm_db_xtp_memory_consumers V NULL
dm_db_xtp_merge_requests V NULL
dm_db_xtp_nonclustered_index_stats V NULL
dm_db_xtp_object_stats V NULL
dm_db_xtp_table_memory_stats V NULL
dm_db_xtp_transactions V NULL
dm_exec_query_profiles V NULL
dm_io_cluster_valid_path_names V NULL
dm_os_buffer_pool_extension_configuration V NULL
dm_resource_governor_resource_pool_volumes V NULL
dm_xtp_gc_queue_stats V NULL
dm_xtp_gc_stats V NULL
dm_xtp_system_memory_consumers V NULL
dm_xtp_threads V NULL
dm_xtp_transaction_recent_rows V NULL
dm_xtp_transaction_stats V NULL
column_store_row_groups V NULL
sp_flush_log X NULL

 

The object types are as follows:

FN = Scalar Function

IF = In-line Table Function

IT = Internal Table

P = Stored Procedure

S = System Table

TF = Table Function

V = View

X = Extended Stored Procedure

Want to watch this in action? Watch here!

Data Platform Virtual Summit 2020

Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.

SQLMaestros YouTube | SQLMaestros Bulletin | SQLMaestros Twitter