Get all user table with total records and their size.

DECLARE @AllTables TABLE([DB Name] sysname,[Table Name] sysname,[Total Rows In Table - Modified] NUMERIC,[Table Size In MB] NVARCHAR(25),[Table Size In GB] NVARCHAR(25))
INSERT @AllTables
EXEC sp_MSforeachdb 'USE ?
SELECT ''?'' AS DBName, s.name + ''.'' + t.Name AS [Table Name],
part.rows AS [Total Rows In Table - Modified],
CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 AS NUMERIC(18, 3)) AS [Tables Total Space In MB],
CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS [Tables Total Space In GB]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.Indexes idx ON t.Object_id = idx.Object_id
INNER JOIN sys.Partitions part ON idx.Object_id = part.Object_id AND idx.Index_id = part.Index_id
INNER JOIN sys.Allocation_units au ON part.Partition_id = au.Container_id
INNER JOIN sys.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
INNER JOIN sys.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 
GROUP BY t.Name, s.name, part.rows
ORDER BY [Tables Total Space In GB] DESC'

SELECT * FROM @AllTables