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
3 Responses to “MSSQL : List all tables with size and row count”
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.
Comment from JohnPB
Time January 25, 2010 at 10:48 am
THANKS !! I was looking for the schema and table name