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:

DECLARE  @Name NVARCHAR(100)

CREATE TABLE #tableInfo
 (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
DECLARE tableCursor CURSOR FOR
SELECT name FROM dbo.sysobjects WHERE type = 'U'

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO #tableInfo
 EXEC dbo.sp_spaceused @Name
FETCH NEXT FROM tableCursor INTO @Name
END
--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', '')

SELECT * FROM #tableInfo ORDER BY DataKB DESC
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!