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
Good morning Uday,
Thank you so much for this article, you just saved me from a bind.