Sometimes we may want to know the space used by a index. We can use the sp_spaceused system stored procedure to measure how much space used by different objects.
This code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are.
SELECT [name],type_desc,space_used_in_kb = (page_count * 8.0),space_used_in_mb = (page_count * 8.0 / 1024.0)
FROM sys.indexes I JOIN sys.dm_db_index_physical_stats(db_id(),object_id('.'),null,null,null) P
ON I.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
This code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are.
SELECT [name],type_desc,space_used_in_kb = (page_count * 8.0),space_used_in_mb = (page_count * 8.0 / 1024.0)
FROM sys.indexes I JOIN sys.dm_db_index_physical_stats(db_id(),object_id('.'),null,null,null) P
ON I.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
No comments:
Post a Comment