SQL SERVER: Index Statistics

Index Statistics.sql

SELECT [schema_name] = s.[name],
	[table_name] = t.[name],
	[index_name] = i.[name],
	st.[user_seeks], 
	st.[user_scans], 
	st.[user_lookups], 
	st.[user_updates],
    STUFF(
        (
            SELECT ', ' + QUOTENAME([c2].[name])
            FROM sys.[index_columns] AS [ic2] WITH (nolock)  
            INNER JOIN sys.[columns] AS [c2] WITH (nolock)  
                ON [ic2].[object_id] = [c2].[object_id] 
                AND [ic2].[column_id] = [c2].[column_id] 
            WHERE [ic2].[object_id] = [i].[object_id]
                AND [ic2].[index_id] = [i].[index_id]
                AND [ic2].[is_included_column] = 0
            ORDER BY [c2].[column_id]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS KeyColumnList,
    STUFF(
        (
            SELECT ', ' + QUOTENAME([c3].[name])
            FROM sys.[index_columns] AS [ic3] WITH (nolock) 
            INNER JOIN sys.[columns] AS [c3] WITH (nolock) ON [ic3].[object_id] = [c3].[object_id] AND [ic3].[column_id] = [c3].[column_id] 
            WHERE [ic3].[object_id] = [i].[object_id]
                AND [ic3].[index_id] = [i].[index_id]
                AND [ic3].[is_included_column] = 1
            ORDER BY [c3].[column_id]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS IncludedColumnList,
	ips.[avg_fragmentation_in_percent], 
	ips.[avg_fragment_size_in_pages], 
	[i].[type], 
	ips.[index_type_desc], 
	ips.[alloc_unit_type_desc], 
	ips.[page_count], 
	[page_count_mb] = CAST(ips.[page_count] / 128.0 as money), 
	ips.[index_depth], 
	--FOLLOWING FIELDS ARE NULL WHEN USING 'LIMITED'
	ips.[record_count], 
	ips.[forwarded_record_count],
	ips.[ghost_record_count],
	ips.[version_ghost_record_count],
	ips.[min_record_size_in_bytes],
	ips.[avg_record_size_in_bytes],
	ips.[max_record_size_in_bytes],
	--FOLLOWING FIELDS ARE NULL WHEN USING 'SAMPLED'
	ips.[fragment_count]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips  -- LIMITED, SAMPLED, DETAILED (SUPER SLOW, AND HEAVY PERFORMANCE COSTS)
INNER JOIN sys.tables t WITH (nolock) ON t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s WITH (nolock) ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes AS i WITH (nolock) ON i.[object_id] = ips.[object_id] AND ips.index_id = i.index_id
INNER JOIN sys.dm_db_index_usage_stats st WITH (nolock) ON [i].[object_id] = [st].[object_id] AND [i].[index_id] = [st].[index_id] 
WHERE ips.database_id = DB_ID() 
	AND st.[database_id] = DB_ID()
	AND t.[object_id] = OBJECT_ID('dbo.Foo')
ORDER BY s.[name],
	t.[name],
	[KeyColumnList],
	[IncludedColumnList]