List all tables with size and row count

Use below script to find the List of all Tables with Size and Row Count:



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


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


INSERT INTO @tbl
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 @tbl 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