am going to post my monstrously big index query.
Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.
Awesome? Oh Really? Why?
This query describes the size, basic definition, location, number of rows, partition status, and enabled/disabled status for all clustered and nonclustered indexes in a database. I typically sort them by descending size, since my primary usage is when a drive space alert fires, or when someone asks one of the million “how much space would it take if we wanted to [x]?” questions.
When you are working with a database which has many indexes that are partitioned over multiple filegroups, which are spread out over multiple drives, this can be very useful when a reindex fails due to a file filling up. Or when you want to estimate how much free space you need to main in a given filegroup in order to be able to reindex the indexes using it.