SELECT
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
(8 * SUM(a.used_pages))/1024 AS 'Indexsize(MB)',
((8 * SUM(a.used_pages))/1024)/1024 AS 'Indexsize(GB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
(8 * SUM(a.used_pages))/1024 AS 'Indexsize(MB)',
((8 * SUM(a.used_pages))/1024)/1024 AS 'Indexsize(GB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
--where OBJECT_NAME(i.OBJECT_ID)='TableName'
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY [Indexsize(MB)] DESC
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY [Indexsize(MB)] DESC
Output
Comments
Post a Comment
If you have any doubt then please let me know in comment section.