There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.
Here are 3 methods but not limited.
1. Get a specific table size
EXEC sp_spaceused N’dbo.MyTable’
2. Get all table size
CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’
SELECT * FROM #TableSize ORDER BY Name ASC
3. Custom Code
SELECT
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
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 i.OBJECT_ID > 255
GROUP BY t.Name
,s.Name
,p.[Rows]
ORDER BY t.Name
afterowl 