Click on the banner to learn about and purchase my database training on Azure

SQL Server – How to list the largest tables and indexes in the database and show the size of each

Views: 0
Reading Time: 4 minutes

Hello everybody!
In this very quick post, I would like to share with you a simple script, showing how to list the largest tables and indexes in the database and return the size of each table and each index, and can also list the objects of all databases of your instance SQL Server.

I had already created some articles related to datafile size and disk size, but I didn't have a specific article for index table size, so I thought it was time to create something like this.

I had also shared this same script in the article. SQL Server - Useful DBA Queries You Always Have to Look for on the Internet, but indexing doesn't work so well to find this script here.

To list the largest tables in the database and return the size of each table, just run the script below on the desired database:

Result:

If you want to list the biggest tables, considering all the databases of your instance, use the script below:

Result:

Another very common need is to find the highest indexes in the bank to analyze if they are still useful or if they are taking up a lot of space unnecessarily.

To do this, just use the script below to return the list of the largest indexes in the database:

Result:

And if you want to perform the same query, in all the databases of your instance, use this script here:

Result:

And that's it folks!
I hope you enjoyed this tip, a big hug and see you next time!