List Hotfixes installed with PowerShell with Get-Hotfix

Have you ever wondered what updates have been applied to your Windows operating system?

Well a quick and easy way to do that is to use the PowerShell command Get-Hotfix. This will list out the description, the HotfixID and when it was installed.

I used this on a few PCs where it appeared that updates were not being applied and it showed me when the last updates were installed and hence helped me identify the PCs which were having the trouble and disconnect them from the network.

1

Find a column in a database with INFORMATION_SCHEMA.COLUMNS

Have you ever just been given a column name and not known which table it was in? Needed to check whether a column existed within a database?…

Well today I was just given a column name and told the information I needed was in there but alas they didn’t know which table it was in.

I could have gone through each table but that is very inefficient and I can more effectively harness the power of SQL to get the answer quickly and easily.

I knew that the INFORMATION_SCHEMA.COLUMNS would be a great place to start and did the below query.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

1

 

The next step was to add the COLUMN_NAME in with a WHERE clause.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = ‘max_history_rows’

2

SQL Server DMVs and DMFs categorised to make it easier to find information.

Have you wanted to find a DMV which would help give information about AlwaysOn Availability Group ,           Change Data Capture , Change Tracking ,Common Language Runtime , Database , Database Mirroring , Execution , Extended Events , Filestream, Full-Text Search and Semantic Search ,        I/O , Indexes , Object s, Query Notifications , Replication , Resource Governor , Security , Service Broker , SQL Server Operating System and Transactions.

Well I have tried to categorise them and have put them into their relevant category.

AlwaysOn Availability Group sys.dm_hadr_auto_page_repair
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_cluster_nodes
sys.dm_hadr_availability_replica_cluster_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_cluster
sys.dm_hadr_cluster_members
sys.dm_hadr_cluster_networks
sys.dm_hadr_database_replica_cluster_states
sys.dm_hadr_database_replica_states
sys.dm_hadr_instance_node_map
sys.dm_hadr_name_id_map
sys.dm_tcp_listener_states
Change Data Capture sys.dm_cdc_errors
sys.dm_cdc_log_scan_sessions
sys.dm_repl_traninfo
Change Tracking sys.dm_tran_commit_table .
Common Language Runtime sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring sys.dm_db_mirroring_auto_page_repair
sys.dm_db_mirroring_connections
Database sys.dm_db_file_space_usage
sys.dm_db_fts_index_physical_stats
sys.dm_db_partition_stats
sys.dm_db_persisted_sku_features
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
sys.dm_db_uncontained_entities
Execution sys.dm_exec_background_job_queue
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_describe_first_result_set
sys.dm_exec_describe_first_result_set_for_object
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_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
Extended Events 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
Filestream and FileTable sys.dm_filestream_file_io_handles
sys.dm_filestream_file_io_requests
Full-Text Search and Semantic Search 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_keywords_by_property
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
Index sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
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
I/O sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Object sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
sys.dm_db_stats_properties
Query Notifications sys.dm_qn_subscriptions
Replication sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Resource Governor sys.dm_resource_governor_configuration
sys.dm_resource_governor_resource_pools
sys.dm_resource_governor_workload_groups
Security sys.dm_audit_actions
sys.dm_audit_class_type_map
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_server_audit_status
Service Broker sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Server Operating System sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_dispatcher_pools
sys.dm_os_hosts
sys.dm_os_latch_stats
sys.dm_os_loaded_modules
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_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_schedulers
sys.dm_os_stacks
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_volume_stats
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_os_windows_info
sys.dm_os_workers
Transaction sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
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