Advertisements
Home > SQL Tips > SQL Tip – How to get size of all Tables in a Database?

SQL Tip – How to get size of all Tables in a Database?


 
You can use SQL Server system Stored Procedure sp_spaceused to get the details of a table like rows and size. Just pass the table name to this SP as a parameter:

USE [AdventureWorks2014]
GO

EXEC sp_spaceused N'Person.Person'
GO
name		rows	reserved	data		index_size	unused
Person.Person	19972   85840 KB	30504 KB	53192 KB	2144 KB

 

But if you want to get details of more than one or all tables of a Database then you can iterate this SP for all tables. To iterate for all tables there is an undocumented function in SQL Server i.e. sp_msForEachTable, and you can provide your sp_spaceused query as a parameter to this SP:

CREATE TABLE #TableSize (
	 name		NVARCHAR(128)
	,rows		CHAR(20)
	,reserved	VARCHAR(18)
	,data		VARCHAR(18)
	,index_size VARCHAR(18)
	,unused		VARCHAR(18)
)

sp_msForEachTable 'INSERT INTO #TableSize (name, rows, reserved, data, index_size, unused) EXEC sp_spaceused [?]'

SELECT * FROM #TableSize 

DROP TABLE #TableSize
GO

 

There is one more way to get the details by using system (sys) views, query below:

SELECT 
     t.name AS TableName 
    ,s.name AS SchemaName 
    ,p.rows AS RowCounts 
    ,SizeInKB = SUM(a.total_pages) * 8
    ,UsedSpaceInKB = SUM(a.used_pages) * 8
    ,UnUsedSpaceInKB = (SUM(a.total_pages) - SUM(a.used_pages)) * 8
    ,SizeInMB = (SUM(a.total_pages) * 8)/1024 
    ,SizeInGB = (SUM(a.total_pages) * 8)/(1024*1024) 
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 

INNER JOIN sys.schemas s 
ON t.schema_id = s.schema_id 

WHERE t.is_ms_shipped = 0 
AND i.object_id > 255 
--AND t.name IN ('tbl1', 'tbl2', 'tbl3', 'tbl4') 
--AND t.name LIKE 'pattern%'  
GROUP BY t.name, s.name, p.rows 
ORDER BY SizeInMB DESC

To get details of selective tables just apply the Table names above in the IN() clause or LIKE operator.


Advertisements
Categories: SQL Tips
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: