Tuesday, May 31, 2011

List all the DMVs and DMFs in SQL Server 2008

You can able to see 136's DMV's and DMF's return for the below query

use master
go
SELECT 'sys.' + name FROM sys.sysobjects WHERE name like 'dm%'


sys.dm_audit_actions


sys.dm_audit_class_type_map

sys.dm_broker_activated_tasks

sys.dm_broker_connections

sys.dm_broker_forwarded_messages

sys.dm_broker_queue_monitors

sys.dm_cdc_errors

sys.dm_cdc_log_scan_sessions

sys.dm_clr_appdomains

sys.dm_clr_loaded_assemblies

sys.dm_clr_properties

sys.dm_clr_tasks

sys.dm_cryptographic_provider_algorithms

sys.dm_cryptographic_provider_keys

sys.dm_cryptographic_provider_properties

sys.dm_cryptographic_provider_sessions

sys.dm_database_encryption_keys

sys.dm_db_file_space_usage

sys.dm_db_index_operational_stats

sys.dm_db_index_physical_stats

sys.dm_db_index_usage_stats

sys.dm_db_mirroring_auto_page_repair

sys.dm_db_mirroring_connections

sys.dm_db_mirroring_past_actions

sys.dm_db_missing_index_columns

sys.dm_db_missing_index_details

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_partition_stats

sys.dm_db_persisted_sku_features

sys.dm_db_script_level

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_plans

sys.dm_exec_connections

sys.dm_exec_cursors

sys.dm_exec_plan_attributes

sys.dm_exec_procedure_stats

sys.dm_exec_query_memory_grants

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_plan

sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_stats

sys.dm_exec_query_transformation_stats

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

sys.dm_exec_trigger_stats

sys.dm_exec_xml_handles

sys.dm_filestream_file_io_handles

sys.dm_filestream_file_io_requests

sys.dm_fts_active_catalogs

sys.dm_fts_fdhosts

sys.dm_fts_index_keywords

sys.dm_fts_index_keywords_by_document

sys.dm_fts_index_population

sys.dm_fts_memory_buffers

sys.dm_fts_memory_pools

sys.dm_fts_outstanding_batches

sys.dm_fts_parser

sys.dm_fts_population_ranges

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_descriptors

sys.dm_os_child_instances

sys.dm_os_cluster_nodes

sys.dm_os_dispatcher_pools

sys.dm_os_dispatchers

sys.dm_os_hosts

sys.dm_os_latch_stats

sys.dm_os_loaded_modules

sys.dm_os_memory_allocations

sys.dm_os_memory_brokers

sys.dm_os_memory_cache_clock_hands

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_entries

sys.dm_os_memory_cache_hash_tables

sys.dm_os_memory_clerks

sys.dm_os_memory_node_access_stats

sys.dm_os_memory_nodes

sys.dm_os_memory_objects

sys.dm_os_memory_pools

sys.dm_os_nodes

sys.dm_os_performance_counters

sys.dm_os_process_memory

sys.dm_os_ring_buffers

sys.dm_os_schedulers

sys.dm_os_spinlock_stats

sys.dm_os_stacks

sys.dm_os_sublatches

sys.dm_os_sys_info

sys.dm_os_sys_memory

sys.dm_os_tasks

sys.dm_os_threads

sys.dm_os_virtual_address_dump

sys.dm_os_wait_stats

sys.dm_os_waiting_tasks

sys.dm_os_worker_local_storage

sys.dm_os_workers

sys.dm_qn_subscriptions

sys.dm_repl_articles

sys.dm_repl_schemas

sys.dm_repl_tranhash

sys.dm_repl_traninfo

sys.dm_resource_governor_configuration

sys.dm_resource_governor_resource_pools

sys.dm_resource_governor_workload_groups

sys.dm_server_audit_status

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

sys.dm_tran_active_snapshot_database_transactions

sys.dm_tran_active_transactions

sys.dm_tran_commit_table

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.dm_xe_map_values

sys.dm_xe_object_columns

sys.dm_xe_objects

sys.dm_xe_packages

sys.dm_xe_session_event_actions

sys.dm_xe_session_events

sys.dm_xe_session_object_columns

sys.dm_xe_session_targets

sys.dm_xe_sessions