Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Microsoft SQL System View Version Map

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
13 Dec 2012CPOL10 min read 18.4K   4   1
MS SQL system views, mapped over version.

Introduction

Im expanding my SQL Meta Data Framework, and need it to be more version independent.
So i had to look at all the system views in the SQL server and find the differences between versions.

I have postet them on my blog here:

System View Groups that has been mapped:

  • Transaction Related Dynamic Management Views and Functions
  • SQL Server Operating System Related Dynamic Management Views
  • Spatial Data Catalog Views
  • Service Broker Catalog Views
  • Semantic Search Catalog Views
  • Security Catalog Views
  • Schemas Catalog Views
  • Scalar Types Catalog Views
  • Replication Related Dynamic Management Views
  • Partition Function Catalog Views
  • Object Catalog Views
  • Linked Servers Catalog Views
  • I/O Related Dynamic Management Views and Functions
  • Full-Text Search Catalog Views
  • Full-Text Search and Semantic Search Catalog Views
  • Extended Properties Catalog Views
  • Extended Events Catalog Views
  • Execution Related Dynamic Management Views and Functions
  • Endpoints Catalog Views
  • Databases and Files Catalog Views
  • Database Related Dynamic Management Views
  • Database Mail Views
  • Data Spaces
  • Data Collector Views
  • Compatibility Views
  • Common Language Runtime Related Dynamic Management Views
  • CLR Assembly Catalog Views
  • Change Tracking Catalog Views
  • AlwaysOn Availability Groups Catalog Views
  • Microsoft SQL System View Version Map
  • XML Schemas (XML Type System) Catalog Views

  • Heres a table view of the systemsviews.

    Color definition
    System views are the same in all versions
    System views are partial the same in all versions
    System view does not exists in this version


    Click on the bull inside of each version to navigate direct to the MSDN page that contains the information.
    Or Click on the More Info in the last column, to see the difference between the versions of the system view.

    SystemView Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Difference Info
    sys.all_columnsMore Info
    sys.all_objects
    sys.all_parameters
    sys.all_sql_modulesMore Info
    sys.all_views
    sys.allocation_units
    sys.assembliesMore Info
    sys.assembly_files
    sys.assembly_modules
    sys.assembly_references
    sys.assembly_typesMore Info
    sys.asymmetric_keysMore Info
    sys.availability_databases_cluster More Info
    sys.availability_group_listener_ip_addresses More Info
    sys.availability_group_listeners More Info
    sys.availability_groups More Info
    sys.availability_groups_cluster More Info
    sys.availability_read_only_routing_lists More Info
    sys.availability_replicas More Info
    sys.backup_devices
    sys.certificatesMore Info
    sys.change_tracking_databases More Info
    sys.change_tracking_tables More Info
    sys.check_constraints
    sys.columnsMore Info
    sys.computed_columnsMore Info
    sys.conversation_endpointsMore Info
    sys.conversation_groups
    sys.conversation_priorities More Info
    sys.credentialsMore Info
    sys.crypt_properties
    sys.cryptographic_providers More Info
    sys.data_spacesMore Info
    sys.database_audit_specification_details More Info
    sys.database_audit_specifications More Info
    sys.database_files
    sys.database_mirroringMore Info
    sys.database_mirroring_endpoints
    sys.database_permissions
    sys.database_principals
    sys.database_recovery_status
    sys.database_role_members
    sys.databasesMore Info
    sys.default_constraints
    sys.destination_data_spaces
    sys.dm_clr_appdomainsMore Info
    sys.dm_clr_loaded_assemblies
    sys.dm_clr_properties
    sys.dm_clr_tasks
    sys.dm_db_file_space_usageMore Info
    sys.dm_db_partition_stats
    sys.dm_db_session_space_usage
    sys.dm_db_task_space_usage
    sys.dm_exec_background_job_queue
    sys.dm_exec_background_job_queue_stats
    sys.dm_exec_cached_plan_dependent_objects
    sys.dm_exec_cached_plansMore Info
    sys.dm_exec_connections
    sys.dm_exec_cursors
    sys.dm_exec_plan_attributesMore Info
    sys.dm_exec_query_memory_grantsMore Info
    sys.dm_exec_query_optimizer_info
    sys.dm_exec_query_plan
    sys.dm_exec_query_resource_semaphoresMore Info
    sys.dm_exec_query_statsMore Info
    sys.dm_exec_requestsMore Info
    sys.dm_exec_sessionsMore Info
    sys.dm_exec_sql_text
    sys.dm_exec_text_query_plan
    sys.dm_exec_xml_handles
    sys.dm_io_backup_tapes
    sys.dm_io_cluster_shared_drives
    sys.dm_io_pending_io_requests
    sys.dm_io_virtual_file_stats
    sys.dm_os_buffer_descriptorsMore Info
    sys.dm_os_child_instances
    sys.dm_os_cluster_nodesMore Info
    sys.dm_os_hosts
    sys.dm_os_latch_stats
    sys.dm_os_loaded_modules
    sys.dm_os_memory_cache_clock_hands
    sys.dm_os_memory_cache_countersMore Info
    sys.dm_os_memory_cache_entriesMore Info
    sys.dm_os_memory_cache_hash_tables
    sys.dm_os_memory_clerksMore Info
    sys.dm_os_memory_objects
    sys.dm_os_memory_pools
    sys.dm_os_performance_counters
    sys.dm_os_schedulersMore Info
    sys.dm_os_stacks
    sys.dm_os_sys_infoMore Info
    sys.dm_os_tasksMore Info
    sys.dm_os_threadsMore Info
    sys.dm_os_virtual_address_dump
    sys.dm_os_wait_stats
    sys.dm_os_waiting_tasks
    sys.dm_os_workersMore Info
    sys.dm_repl_articles
    sys.dm_repl_schemas
    sys.dm_repl_tranhash
    sys.dm_repl_traninfoMore Info
    sys.dm_tran_active_snapshot_database_transactions
    sys.dm_tran_active_transactionsMore Info
    sys.dm_tran_current_snapshot
    sys.dm_tran_current_transaction
    sys.dm_tran_database_transactions
    sys.dm_tran_locks
    sys.dm_tran_session_transactions
    sys.dm_tran_top_version_generators
    sys.dm_tran_transactions_snapshot
    sys.dm_tran_version_store
    sys.endpoint_webmethods
    sys.endpoints
    sys.event_notifications
    sys.eventsMore Info
    sys.extended_procedures
    sys.extended_properties
    sys.filegroupsMore Info
    sys.foreign_key_columns
    sys.foreign_keys
    sys.fulltext_catalogsMore Info
    sys.fulltext_document_types
    sys.fulltext_index_catalog_usages
    sys.fulltext_index_columnsMore Info
    sys.fulltext_index_fragments More Info
    sys.fulltext_indexesMore Info
    sys.fulltext_languages
    sys.fulltext_semantic_language_statistics_database More Info
    sys.fulltext_semantic_languages More Info
    sys.fulltext_stoplists More Info
    sys.fulltext_stopwords More Info
    sys.fulltext_system_stopwords More Info
    sys.function_order_columns More Info
    sys.functions
    sys.http_endpoints
    sys.identity_columnsMore Info
    sys.index_columnsMore Info
    sys.indexesMore Info
    sys.internal_tables
    sys.key_constraints
    sys.key_encryptions
    sys.linked_logins
    sys.master_files
    sys.master_key_passwords
    sys.message_type_xml_schema_collection_usages
    sys.numbered_procedure_parameters
    sys.numbered_procedures
    sys.objects
    sys.parametersMore Info
    sys.partition_parametersMore Info
    sys.partition_range_values
    sys.partition_schemesMore Info
    sys.partitionsMore Info
    sys.procedures
    sys.registered_search_properties More Info
    sys.registered_search_property_lists More Info
    sys.remote_logins
    sys.remote_service_bindings
    sys.routes
    sys.rules
    sys.schemas
    sys.server_audit_specification_details More Info
    sys.server_audit_specifications More Info
    sys.server_audits More Info
    sys.server_event_session_actions More Info
    sys.server_event_session_events More Info
    sys.server_event_session_fields More Info
    sys.server_event_session_targets More Info
    sys.server_event_sessions More Info
    sys.server_file_audits More Info
    sys.server_permissions
    sys.server_principalsMore Info
    sys.server_role_members
    sys.serversMore Info
    sys.service_contract_message_usages
    sys.service_contract_usages
    sys.service_contracts
    sys.service_message_types
    sys.service_queue_usages
    sys.service_queuesMore Info
    sys.services
    sys.soap_endpoints
    sys.spatial_index_tessellations More Info
    sys.spatial_indexes More Info
    sys.sql_dependencies
    sys.sql_expression_dependencies More Info
    sys.sql_loginsMore Info
    sys.sql_modulesMore Info
    sys.statsMore Info
    sys.stats_columns
    sys.symmetric_keysMore Info
    sys.synonyms
    sys.sysalt_files
    sys.syscache_objects
    sys.syscharsets
    sys.syscolumns
    sys.syscomments
    sys.sysconfigures
    sys.sysconstraints
    sys.syscur_configs
    sys.sysdatabases
    sys.sysdependsMore Info
    sys.sysdevices
    sys.sysfile_groups
    sys.sysfiles
    sys.sysforeign_keys
    sys.sysfulltext_catalogs
    sys.sysindexesMore Info
    sys.sysindexkeys
    sys.syslanguages
    sys.syslock_info
    sys.syslogins
    sys.sysmembers
    sys.sysmessages
    sys.sysobjects
    sys.sysoledbusers
    sys.sysperf_info
    sys.syspermissions
    sys.sysprocesses
    sys.sysprotects
    sys.sysreferences
    sys.sysremote_logins
    sys.sysserversMore Info
    sys.system_columnsMore Info
    sys.system_components_surface_area_configuration
    sys.system_functions
    sys.system_objects
    sys.system_parameters
    sys.system_stored_procedures
    sys.system_views
    sys.systypesMore Info
    sys.sysusers
    sys.table_types More Info
    sys.tablesMore Info
    sys.tcp_endpoints
    sys.transmission_queueMore Info
    sys.trigger_event_types More Info
    sys.trigger_eventsMore Info
    sys.triggers
    sys.typesMore Info
    sys.via_endpoints
    sys.views
    syscollector_collection_items More Info
    syscollector_collection_sets More Info
    syscollector_collector_types More Info
    syscollector_config_store More Info
    syscollector_execution_log More Info
    syscollector_execution_log_full More Info
    syscollector_execution_stats More Info
    sysmail_allitems
    sysmail_event_log
    sysmail_faileditems
    sysmail_mailattachments
    sysmail_sentitems
    sysmail_unsentitems

    Hope it helps you as much as it has me... ;)

    History

    13-12-2012: Added link to the XML Schemas (XML Type System) Catalog Views

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Software Developer
    Denmark Denmark

    Comments and Discussions

     
    GeneralMy vote of 5 Pin
    daveynogoodboyo21-Feb-13 12:24
    daveynogoodboyo21-Feb-13 12:24 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.