5 out of 8 rated this helpful - Rate this topic

System Views (Windows Azure SQL Database)

Microsoft Windows Azure SQL Database provides support for some of the SQL Server built-in system views. This topic describes the support for the system views.

The following table lists the system view categories and also describes whether Windows Azure SQL Database provides support for each category.

Category	Windows Azure SQL Database Support
SQL Database Views	Yes
Catalog Views	Partial
Compatibility Views	Partial
Database Mail Views	No
Data Collector Views	No
Policy-Based Management Views	No
Dynamic Management Views	Partial
Information Schema Views	Yes
Replication Views	No

Important
For more information about Transact-SQL grammar, system view usage, and examples, see System Views (Transact-SQL) in SQL Server Books Online.

SQL Database Views

Several views are specific to Windows Azure SQL Database and are not supported on in-premise SQL Server. These views are listed in the following tables:

sys.bandwidth_usage (Windows Azure SQL Database)	sys.dm_federation_operation_members (Windows Azure SQL Database)	sys.federation_members (Windows Azure SQL Database)
sys.database_usage (Windows Azure SQL Database)	sys.dm_federation_operations (Windows Azure SQL Database)	sys.federation_member_distributions (Windows Azure SQL Database)
sys.dm_database_copies (Windows Azure SQL Database)	sys.federations (Windows Azure SQL Database)	sys.federation_member_distribution_history (Windows Azure SQL Database)
sys.dm_db_objects_impacted_on_version_change (Windows Azure SQL Database)	sys.federation_distributions (Windows Azure SQL Database)	sys.federation_member_history (Windows Azure SQL Database)
sys.dm_federation_operation_errors (Windows Azure SQL Database)	sys.federation_distribution_history (Windows Azure SQL Database)	sys.firewall_rules (Windows Azure SQL Database)
sys.dm_federation_operation_error_members (Windows Azure SQL Database)	sys.federation_history (Windows Azure SQL Database)	sys.database_firewall_rules (Windows Azure SQL Database)
sys.resource_stats	sys.resource_usage	sys.dm_operation_status (Windows Azure SQL Database)
sys.database_connection_stats	sys.event_log

Catalog Views

Catalog views return information that is used by the underlying SQL Server database engine. The following table lists the supported catalog views in Windows Azure SQL Database.

sys.all_columns	sys.foreign_key_columns	sys.synonyms
sys.all_objects	sys.foreign_keys	sys.system_columns
sys.all_parameters	sys.identity_columns	sys.system_objects
sys.all_sql_modules	sys.index_columns	sys.system_parameters
sys.all_views	sys.indexes	sys.system_sql_modules
sys.check_constraints	sys.key_constraints	sys.system_views
sys.columns	sys.objects	sys.table_types
sys.computed_columns	sys.parameters	sys.tables
sys.database_permissions	sys.plan_guides	sys.trigger_event_types
sys.database_principals	sys.procedures	sys.trigger_events
sys.database_role_members	sys.schemas	sys.triggers
sys.databases (Windows Azure SQL Database)	sys.spatial_reference_systems	sys.types
sys.default_constraints	sys.sql_logins	sys.views
sys.event_notification_event_types	sys.sql_modules	sys.xml_schema_collections
sys.event_notifications	sys.stats
sys.events	sys.stats_columns

Windows Azure SQL Database does not support the following system catalog views.

sys.allocation_units	sys.fulltext_stopwords	sys.server_role_members
sys.assemblies	sys.fulltext_system_stopwords	sys.server_sql_modules
sys.assembly_files	sys.function_order_columns	sys.server_trigger_events
sys.assembly_modules	sys.http_endpoints	sys.server_triggers
sys.assembly_references	sys.internal_tables	sys.servers
sys.assembly_types	sys.key_encryptions	sys.service_broker_endpoints
sys.asymmetric_keys	sys.linked_logins	sys.service_contract_message_usages
sys.backup_devices	sys.master_files	sys.service_contract_usages
sys.certificates	sys.master_key_passwords	sys.service_contracts
sys.change_tracking_database	sys.message_type_xml_schema_collection_usages	sys.service_message_types
sys.change_tracking_tables	sys.messages	sys.service_queue_usages
sys.column_type_usages	sys.module_assembly_usages	sys.service_queues
sys.column_xml_schema_collection_usages	sys.numbered_procedure_parameters	sys.services
sys.configurations	sys.numbered_procedures	sys.soap_endpoints
sys.conversation_endpoints	sys.openkeys	sys.spatial_index_tessellations
sys.conversation_groups	sys.parameter_xml_schema_collection_usages	sys.spatial_indexes
sys.conversation_priorities	sys.paramter_type_usages	sys.symmetric_keys
sys.credentials	sys.partition_functions	sys.system_components_surface_area_configuration
sys.crypt_properties	sys.partition_parameters	sys.tcp_endpoints
sys.cryptographic_providers	sys.partition_range_values	sys.trace_categories
sys.data_spaces	sys.partition_schemes	sys.trace_columns
sys.database_audit_specification_details	sys.partitions	sys.trace_event_bindings
sys.database_audit_specifications	sys.remote_logins	sys.trace_events
sys.database_files	sys.remote_service_bindings	sys.trace_subclass_values
sys.database_mirroring	sys.resource_governor_configuration	sys.traces
sys.database_mirroring_endpoints	sys.resource_governor_resource_pools	sys.transmission_queue
sys.database_mirroring_witnesses	sys.resource_governor_workload_groups	sys.type_assembly_usages
sys.database_recovery_status	sys.routes	sys.via_endpoints
sys.destination_data_spaces	sys.securable_classes	sys.xml_indexes
sys.endpoint_webmethods	sys.server_assembly_modules	sys.xml_schema_attributes
sys.endpoints	sys.server_audit_specifications_details	sys.xml_schema_collections
sys.extended properties	sys.server_audits	sys.xml_schema_component_placements
sys.extended_procedures	sys.server_event_notifications	sys.xml_schema_components
sys.filegroups	sys.server_event_session_fields	sys.xml_schema_elements
sys.fulltext_catalogs	sys.server_event_session_targets	sys.xml_schema_facets
sys.fulltext_document_types	sys.server_event_sessions	sys.xml_schema_model_groups
sys.fulltext_index_catalog_usages	sys.server_event_sessions_actions	sys.xml_schema_namespaces
sys.fulltext_index_columns	sys.server_event_sessions_events	sys.xml_schema_types
sys.fulltext_index_fragments	sys.server_events	sys.xml_schema_wildcard_namespaces
sys.fulltext_indexes	sys.server_file_audits	sys.xml_schema_wildcards
sys.fulltext_languages	sys.server_permissions
sys.fulltext_stoplists	sys.server_principals

Compatibility Views

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only.

Windows Azure SQL Database supports only sys.syscharsets, sys.syslanguages, and sys.systypes in this category.

Dynamic Management Views

Dynamic management views return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. In this release, Windows Azure SQL Database provides partial support for only three categories of dynamic management views: Database-Related Dynamic Management Views, Execution-Related Dynamic Management Views, and Transaction-Related Dynamic Management Views.

Note
In Windows Azure SQL Database, querying a dynamic management view requires VIEW DATABASE STATE permission.

Database-Related Dynamic Management Views

The following table lists the supported database-related dynamic management views in Windows Azure SQL Database.

sys.dm_db_partition_stats

Windows Azure SQL Database does not support the following database-related dynamic management views.

sys.dm_db_file_space_usage	sys.dm_db_session_space_usage
sys.dm_db_persisted_sku_features	sys.dm_db_task_space_usage
sys.dm_db_objects_disabled_on_compatibility_level_change

Execution-Related Dynamic Management Views

The following table lists the supported execution-related dynamic management views in Windows Azure SQL Database.

sys.dm_exec_connections (Windows Azure SQL Database)

sys.dm_exec_describe_first_result_set

sys.dm_exec_describe_first_result_set_for_object

sys.dm_exec_query_plan

sys.dm_exec_query_stats

sys.dm_exec_requests (Windows Azure SQL Database)

sys.dm_exec_sessions (Windows Azure SQL Database)

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

Windows Azure SQL Database does not support the following execution-related dynamic management views.

sys.dm_exec_background_job_queue	sys.dm_exec_plan_attributes
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_query_resource_semaphores
	sys.dm_exec_query_optimizer_info
sys.dm_exec_cached_plan_dependent_objects	sys.dm_exec_xml_handles
sys.dm_exec_cursors

Transaction-Related Dynamic Management Views

The following table lists the supported transaction-related dynamic management views in Windows Azure SQL Database.

sys.dm_tran_active_transactions (Windows Azure SQL Database)

sys.dm_tran_database_transactions (Windows Azure SQL Database)

sys.dm_tran_locks

sys.dm_tran_session_transactions

Windows Azure SQL Database does not support the following transaction-related dynamic management views.

sys.dm_tran_active_snapshot_database_transactions	sys.dm_tran_transactions_snapshot
sys.dm_tran_current_snapshot	sys.dm_tran_version_store
sys.dm_tran_current_transaction	sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_top_version_generators

Information Schema Views

Information schema views provide information regarding metadata. The following table lists the supported information schema views in Windows Azure SQL Database.

CHECK_CONSTRAINTS	DOMAINS	TABLE_CONSTRAINTS
COLUMN_DOMAIN_USAGE	KEY_COLUMN_USAGE	TABLE_PRIVILEGES
COLUMN_PRIVILEGES	PARAMETERS	TABLES
COLUMNS	REFERENTIAL_CONSTRAINTS	VIEW_COLUMN_USAGE
CONSTRAINT_COLUMN_USAGE	ROUTINE_COLUMNS	VIEW_TABLE_USAGE
CONSTRAINT_TABLE_USAGE	ROUTINES	VIEWS
DOMAIN_CONSTRAINTS	SCHEMATA

Community Additions

ADD