Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 10, 2000

Finding the Biggest Tables in a Database

By Bill Graziano

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM