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.
