Script to view the size of all tables in a database

I’ve got a 300 GB database on my local PC containing lots of tables and I wanted to see if there are any tables I can clear down to reduce the size, e.g. log tables. This script will return a list of all the tables in a specific database ordered by the size.

SELECT IDENTITY(INT, 1, 1) AS Id, schema_name(schema_id) as SchemaName, name as TableName
INTO #tables
FROM sys.tables
WHERE type = 'U';
DECLARE @Counter INT = 1;
DECLARE @MaxCounter INT = (SELECT MAX(Id) FROM #tables);
DECLARE @Table VARCHAR(100)

CREATE TABLE #TableSize
(
 name VARCHAR(100)
 ,rows int
 ,reserved VARCHAR(100)
 ,data VARCHAR(100)
 ,index_size VARCHAR(100)
 ,unused VARCHAR(100)
);
WHILE @Counter <= @MaxCounter
BEGIN
SELECT @Table = SchemaName + '.' + TableName
 FROM #tables
 WHERE Id = @Counter;
INSERT INTO #TableSize
 EXEC sp_spaceused @Table;
SET @Counter += 1;
END;
SELECT *
FROM #TableSize
ORDER BY CAST(SUBSTRING(index_size, 1, LEN(index_size) - 3) AS INT) DESC;
DROP TABLE #tables;
DROP TABLE #TableSize;

Updated on 24/09/2013

I’ve just found this

EXEC sp_msforeachtable 'EXEC sp_spaceused [?];

It’s undocumented but might be easier that doing the loop above.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s