--Object metadata
SELECT * FROM sys.databases
SELECT * FROM sys.schemas
SELECT * FROM sys.objects
SELECT * FROM sys.tables
SELECT * FROM sys.columns
SELECT * FROM sys.identity_columns
SELECT * FROM sys.foreign_keys
SELECT * FROM sys.foreign_key_columns
SELECT * FROM sys.default_constraints
SELECT * FROM sys.check_constraints
SELECT * FROM sys.indexes
SELECT * FROM sys.index_columns
SELECT * FROM sys.triggers
SELECT * FROM sys.views
SELECT * FROM sys.procedures

--Database Diagnostics
--Database size
SELECT * FROM sys.database_files
SELECT * FROM sys.partitions
SELECT * FROM sys.allocation_units

SELECT object_name(a.object_id), c.name, SUM(rows) rows, SUM(total_pages) total_pages, SUM(used_pages) used_pages, SUM(data_pages) data_pages
FROM sys.partitions a INNER JOIN sys.allocation_units b ON a.hobt_id = b.container_id
    INNER JOIN sys.indexes c ON a.object_id = c.object_id and a.index_id = c.index_id
GROUP BY object_name(a.object_id), c.name
ORDER BY object_name(a.object_id), c.name

--Indexes
SELECT * FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
SELECT * FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)

SELECT * FROM sys.dm_db_index_usage_stats
SELECT * FROM sys.dm_db_missing_index_details
SELECT * FROM sys.dm_db_missing_index_group_stats
SELECT * FROM sys.dm_db_missing_index_groups

USE AdventureWorks
GO

SELECT City,PostalCode 
FROM Person.Address 
WHERE City IN ('Seattle','Atlanta')

SELECT City,PostalCode,AddressLine1 
FROM Person.Address 
WHERE City = 'Atlanta'

SELECT City,PostalCode,AddressLine1 
FROM Person.Address 
WHERE City like 'Atlan%'
GO 100

SELECT * 
FROM 
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, 
migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv 
	INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

--Queries
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_connections
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_exec_cached_plans
SELECT * FROM sys.dm_exec_query_stats
SELECT * FROM sys.dm_exec_procedure_stats

SELECT * FROM sys.dm_exec_query_plans(<plan_handle>)
SELECT * FROM sys.dm_exec_sql_text(<sql_handle>)

SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
SELECT query_plan, text, * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)