SQL SERVER – The Older Way to Find Size of Index Using Deprecated sysindexes

Earlier this month, I wrote a blog post about How to Find Size of the Index for Tables? – Interview Question of the Week #198, in this I had explained the way to find the size of the index for the table. However, while I was writing the blog post, I was thinking about how I used to love sysindexes once upon a time. However, as now I focus on the new DMVs, I decided not to provide the script of the sysindexes.

However, Wilfred van Dijk, post a very interesting comment on the earlier blog post and he did point out that deprecated sysindexes was much more easier to find the size of the index. I totally agree with the note and after seeing the comment, I decided to post the script on the blog post.

Please note, that I do not encourage you to use Deprecated features, I still suggest you use the newly provided script in the blog post here for all your production needs.

Here is the script provided by Wilfred.

SELECT object_schema_name(id) AS SchemaName,
object_name(id) AS TableName,
name AS IndexName,
dpages/128 AS IndexSizeMB, rowcnt
FROM sysindexes
WHERE indid IN (1,2)
AND FirstIAM IS NOT NULL
AND objectproperty(id, 'IsUserTable') = 1
ORDER BY 1,2

If you are going to leave a comment that sysindexes is deprecated and we should not use it, please save the energy. We totally agree with you. This blog post is just for the reason for nostalgia reasons.

Reference: Pinal Dave (https://blog.sqlauthority.com)