Finding the biggest tables in a database

By Bill Graziano on 4 August 2000 | Tags: Storage/Sizing

This quick article includes a discussion on SQL Server tools to determine the size of tables in a database. It also has a downloadable script I wrote to find the largest tables in a database.

In one of my projects, I'm working with a piece of packaged software. This software has roughly 700 tables in it. We spent about two weeks installing the database and configuring it. As part of this process we loaded numerous "lookup" tables. We finished with a 2.5GB database. This was before we had loaded a single record or converted any data from our existing system. Uh oh.

So I set out trying to find out where all this data was. I started with Enterprise Manager. If you select the name of a database, the right hand pane will show an information screen about that database. The second "tab" lists tables and index sizes. Unfortunately they were in alphabetical order. After I scrolled through the first hundred or so I gave up.

Next I turned to sp_spaceused. This is a nifty little stored procedure that will tell you how big your datbase is. If you give it the name of a table as a parameter it will display the size of that table. If you need to know the size of a specific table this is a great little utility. It still wasn't what I was looking for but it was close.

One nice feature about SQL Server is the source code for system stored procedures, such as sp_spaceused, is readily available. It is just a stored procedure in the master database that can be opened and viewed just like any other stored procedure.

So I took this code and modfied it for my needs. BigTables.sql will run through your database and display information about your 25 largest tables. It's pretty easy to change the number of tables displayed. You could also turn this into a parameterized stored procedure if you wanted.

I used this tool and found that 1.4GB of my data was stored in two tables. A little more research showed we could change some application options and cut these tables in half. Not bad for a few hours work.

Update (11/26/2001): Brooks updated this script to add some additional reporting. He added a column to show the percentage of the space that an index is taking up and the percentage of the reserved space that is used. You can see his script in Script Library forum (here).