HOW TO DETERMINE SIZE OF ALL TABLES IN A DATABASE (T-SQL, SQL Table Size)

Sometimes, it's very useful to have a script for performance tuning with you. Here we face the challange to find out the size of every single tables in a database listing very specific measures as well (indexes, unused, reserved, data space).

The solution is very simple if we try to list only one table using sp_spaceused store procedure:


USE AdventureWorks2008R2
GO
EXEC sp_spaceused 'Person.Address'

This works only for current active database and if we want to get table that has the same name, but it's located in other database we need to use full table path:


USE AdventureWorks2008R2
GO
EXEC AdventureWorks.sys.sp_spaceused 'Person.Address'


So, a big challenge comes up here when we want to do that for all tables in database. Here is the complete script where I used stuffs like: executing dynamic strings, cursors temporarily tables.


DECLARE @databaseName varchar(255)
DECLARE @tableName varchar(255)
DECLARE @cmd nvarchar(2000)

SET @databaseName = 'AdventureWorks2008R2'

IF OBJECT_ID('tempdb..#tempTables') IS NOT NULL
DROP TABLE #tempTables

CREATE TABLE #tempTables(TableName varchar(255))

SET @cmd = 'INSERT #tempTables(TableName)
SELECT FullTablePath = '''+@databaseName+'.'' + s.name + ''.'' + o.name FROM '+@databaseName+ '.sys.objects o
INNER JOIN '+@databaseName+ '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type LIKE ''U''
ORDER BY s.name, o.name'


EXECUTE (@cmd)



IF OBJECT_ID('tempdb..#tempResults') IS NOT NULL
DROP TABLE #tempResults

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


DECLARE tableCursor CURSOR FOR
SELECT TableName FROM #tempTables
FOR READ ONLY

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE (@@Fetch_Status >= 0)
BEGIN

SET @cmd = 'INSERT #tempResults

EXEC '+@databaseName+'.sys.sp_spaceused '''+ @tableName + ''''
PRINT @cmd
EXEC (@cmd)

FETCH NEXT FROM tableCursor INTO @tableName
END



CLOSE tableCursor
DEALLOCATE tableCursor


SELECT
*,
DataSizeInKB = dataSize,
DataSizeInMB = CAST(CAST(REPLACE(dataSize, ' KB','') as int) /1024 AS VARCHAR(25)) + ' MB'
FROM #tempResults
ORDER BY CAST(REPLACE(dataSize, ' KB','') as int) DESC

DROP TABLE #tempTables
DROP TABLE #tempResults



And the lucky winner is "Person" table!
Also, here is missing schema name which you can add for fun modifying the script.