Determine the Space Used by Tables

Eventually you’ll be faced with running out of space on a SQL server.  Usually, before you get more drive space, you’re asked to find out if you can get rid of any information you’re holding onto in the server.  You come up with a list of tables in databases that you feel you could remove for now, since you have a backup of them.

You do have a backup, right?

The problem is, you only want to delete those tables that will really make a difference.  But how can you tell which ones will free the most space if you were to delete them?

If you want to find the size of your tables, then you’ll need to know about the built in stored procedure sp_spaceUsed. You pass it the table name in questions,  and it returns the number of rows, now much space is reserved (in KB), how much of that is used by the data, how much is used by indexes, and how much is pre-allocated space.

exec sp_spaceused @Name

That’s all well and good, but I have thousands of tables to deal with.  How can I run that for all of the tables in a database, and sort it by size?

Try this:


 (Name NVARCHAR(100)
 , RowsCount INT
 , ReservedKB VARCHAR(15)
 , DataKB VARCHAR(15)
 , IndexKB VARCHAR(15)
 , UnusedKB VARCHAR(15)

-- Run a cursor through all of the tables
SELECT name FROM dbo.sysobjects WHERE type = 'U'

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @Name
 INSERT INTO #tableInfo
 EXEC dbo.sp_spaceused @Name
FETCH NEXT FROM tableCursor INTO @Name
--end loop

--clean up
CLOSE tableCursor
DEALLOCATE tableCursor

UPDATE #tableInfo
 SET ReservedKB = REPLACE(ReservedKB, ' KB', '')
 , DataKB = REPLACE(DataKB, ' KB', '')
 , IndexKB = REPLACE(IndexKB, ' KB', '')
 , UnusedKB = REPLACE(UnusedKB, ' KB', '')

DROP TABLE #tableInfo

You should now have a nice table showing you your largest tables first.  This can also be useful for tracking how tables grow over time.  If you were to use this to write a report monthly, you could compare one month to the next and see which tables are growing more rapidly… That would be useful if you had multiple hard drives in your server.  You could physically allocate your largest growing databases to your largest discs.

Useful information indeed!

Well, if you have any questions, please let me know.  I’m here to help you learn all about SQL!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *