Ana Sayfa > DenaliİpucuSQL ServerT-SQL > SQL Server Denali ile gelen yeni System Objeleri

SQL Server Denali ile gelen yeni System Objeleri

Bu yazıda Aaron Bertrand ın bloğunda gördüğüm, aynı zamanda iki veritabanının kıyaslanmasında da kullanabileceğimiz bir yöntemi paylaşmak istiyorum.

2008 R2 instance ‘ı ile SQL  Server Denali instance ındaki sistem objelerini, yazacağımız bir sorgu ile eşleştirip, yeni sistem objelerini, kaldırılmış ve değişikliğe uğramış system obje lerini tesbit edebiliriz.

SQL Server DENALI Yeni eklenen System Objeleri

SELECT
    [name] = N'sys.' + o.name,
    [type] = o.type_desc
FROM
    [master].sys.all_objects AS o
LEFT OUTER JOIN
    [ORHAN\2008R2].[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;

 

Sorgu sonucunda DENALI ile gelen yeni sistem objelerini listelemiş oluyoruz:

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

 

 SQL Server DENALI kaldırılan System Objeleri

Denali instance da çalıştırılacak bu sorgu, SQL Server 2008 R2 ile karşılaştırıldığında yeni veya değiştirilmiş sütunları içerecektir.

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;

 

Adı

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
 
SQL Server “Denali” – DMV / Catalog View, Eklenen ve değişen Kolonlar
 
;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;

Değişilik Listesi:

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

Benzer yazılar

Yorum Yaz