/****************************************************************/
/************** Index Information *******************************/
/****************************************************************/
SELECT  DB_NAME (DB_ID())			AS 'Database_Name'
      ,	OBJECT_SCHEMA_NAME(i.object_id)		AS 'Schema_Name'
      , OBJECT_NAME(i.object_id)		AS 'Object_Name'
      , i.name					AS 'Index_Name'
      , i.is_primary_key			AS 'IsPrimaryKey'
      , i.is_unique				AS 'IsUniqueIndex'
      , c.name					AS 'Column_Name'
      , ic.is_included_column			AS 'IsIncludedColumn'
      , ic.key_ordinal				AS 'Column_Order'
	  , i.type_desc				AS 'Index_Type'
	  , STATS_DATE(i.object_id, i.index_id) AS 'lastupdated'
FROM    sys.indexes i
        INNER JOIN sys.index_columns ic
            ON i.object_id = ic.object_id
               AND i.index_id = ic.index_id
        INNER JOIN sys.columns c
            ON ic.object_id = c.object_id
               AND ic.column_id = c.column_id
        INNER JOIN sys.objects o
            ON i.object_id = o.object_id
WHERE   o.is_ms_shipped = 0 and o.type = 'U'
ORDER BY    OBJECT_SCHEMA_NAME(i.object_id) 
	  , OBJECT_NAME(i.object_id) 
	  , i.is_primary_key DESC
          , i.is_unique DESC
          , i.name 
          , ic.key_ordinal;

/****************************************************************/
/************** Table Wise Index Count and Size *****************/
/****************************************************************/
SELECT	Table_Name	AS 'Table_Name',
	COUNT(1)	AS 'Index_Count',
	CAST(SUM(IndexSizeKB)/1024.00 AS DECIMAL(16,2))			AS 'Total_Index_Size_MB',
	CAST(SUM(IndexSizeKB)/(1024.00*1024.00) AS DECIMAL(16,2))	AS 'Total_Index_Size_GB'
FROM(
	SELECT 
		OBJECT_NAME(I.Object_id)	AS 'Table_Name',
		i.[name]			AS 'IndexName' ,
		SUM(s.[used_page_count]) * 8	AS 'IndexSizeKB'
	FROM	sys.dm_db_partition_stats s
		INNER JOIN sys.indexes AS i 
			ON s.[object_id] = i.[object_id]
				AND s.[index_id] = i.[index_id]
		INNER JOIN sys.objects o
			ON i.object_id = o.object_id
	WHERE i.Name IS NOT NULL AND o.is_ms_shipped = 0 and o.type = 'U'
	GROUP BY OBJECT_NAME(I.Object_id),i.[name]
) A
GROUP BY TABLE_NAME
ORDER BY Total_Index_Size_GB DESC;
GO

/****************************************************************/
/************** Index Fragmentation Details *********************/
/****************************************************************/
SELECT	object_schema_name(ips.object_id)	AS 'Schema_Name',
	object_name (ips.object_id)		AS 'Object_Name',
	i.name					AS 'Index_Name',
	ips.avg_fragmentation_in_percent	AS 'Avg_Fragmentation%',
	ips.page_count				AS 'Page_Count',
	CASE	WHEN (ips.avg_fragmentation_in_percent BETWEEN 5 AND 30) AND ips.page_count > 1000
			THEN 'Reorganize'
		WHEN ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000 
			THEN 'Rebuild'
		ELSE	     'Healthy'
	END AS 'Index_Status'
FROM	sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ips
	INNER JOIN sys.indexes i
		ON i.object_id = ips.object_id 
		   AND i.index_id = ips.index_id
WHERE	ips.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC;