Determing SQL Server Table Size

This is a bit of code I found that determines the size used by each table in your database.

Make sure you run DBCC UPDATEUSAGE first to correct any incorrect stats (pages etc) on your tables.

DBCC UPDATEUSAGE (YOUR DATABASE NAME)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status >= 0)
BEGIN
INSERT  #TempTable
EXEC sp_spaceused @TableName
FETCH NEXT FROM tableCursor INTO @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults, ordered by biggest
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC

DROP TABLE #TempTable

References:

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx