List Table Size for all Tables in SQL Database

by
When you feel the size of your SQL Database growing larger, you need to find the reason for what are the tables taking more storage memory space in your database. I am also came through the same situation in my customer end, because he is using SQL Server 2008 R2 Express. As you know, this is a free version, so it will allow only 20 GB storage size for every single database. I have got the following T-SQL query to list the size of all the tables in a sql databse and I have modified the query to list larger size tables first by ORDER BY statement, so that we can easily identify which tables are taking high storage memory size.

T-SQL: List Used and Unused Size for all Tables

USE [MorganDB];
GO
SELECT t.NAME AS TableName, p.rows AS RowsCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB Desc

Output: List total space, used size and unused size for all tables

List Table Size for all Tables in SQL Database

SQL Server query to get Complete Database size

You can use the built in stored procedure sp_spaceused to get the total space used by a SQL Database.

USE [MorganDB];
GO
exec sp_spaceused

Output: Returns total Database size

sql server query to get database size in sql

Thanks,
Morgan
Software Developer

Related Posts

Advertisement

Leave a Comment