MSSQL : List all tables with size and row count

By admin - Last updated: Sunday, December 13, 2009 - Save & Share - 3 Comments

It comes to be very handy while analyzing the database tables.
Here is a quick and dirty script lists all the tables and their rows counts, data size.

DECLARE @table table(Id int IDENTITY(1,1)
					, Name varchar(256))

INSERT INTO @table
SELECT b.name + '.'+ a.name	
FROM sys.tables a INNER JOIN sys.schemas b
		ON a.schema_id = b.schema_id

INSERT INTO @table
SELECT '-1'

DECLARE @result table(	TableName varchar(256)
						, TotalRows int
						, Reserved varchar(50)
						, DataSize varchar(50)
						, IndexSize varchar(50)
						, UnusedSize varchar(50))
						
						
DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1

WHILE 1=1
BEGIN
	SELECT @temp = Name
	FROM @table
	WHERE Id = @index
	
	IF @temp = '-1'
		BREAK	
	
	INSERT @result(	TableName
					, TotalRows
					, Reserved
					, DataSize
					, IndexSize
					, UnusedSize)
	EXEC sp_spaceused @temp
	
	SET @index = @index + 1
END

SELECT c.name+'.'+b.name as [table]
		, a.*
 FROM @result a 
		INNER JOIN sys.tables b
			ON a.TableName = b.name	
		INNER JOIN sys.schemas c
		ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC



Posted in SQL • Tags: Top Of Page

3 Responses to “MSSQL : List all tables with size and row count”

Comment from JohnPB
Time January 25, 2010 at 10:48 am

THANKS !! I was looking for the schema and table name

Comment from MikeS
Time July 21, 2012 at 1:30 am

Thank you – this query was very helpful!

Comment from mainesysadmin
Time June 17, 2014 at 5:10 am

Thank you, this query worked great for me in SQL 2008 R2.

Write a comment


Captcha: × 9 = eighteen