TSQL script to get the index size and information in SQL Server

TSQL script to get the index size and information in SQL Server post can help you in getting the index information from a database in SQL Server. There are two scripts provided:

Script 1: It captures all possible information of all indexes in the given database

Script 2: Captures the table wise number of indexes and total index size on a table

Script 3: Captures the index fragmentation details in the given database

TSQL script to get the index information in SQL Server:

/****************************************************************/
/************** 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;

TSQL script to get the index size and count in SQL Server:

/****************************************************************/
/************** 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

TSQL script to get the index fragmentation details in SQL Server:

/****************************************************************/
/************** 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;

Here is the script file: TSQL script to get the index size and information in SQL Server