declare @t table(TableName nvarchar(128),NumRecords int)
insert into @t exec sp_MSforeachtable
'SELECT ''?'' TableName, Count(1) NumRecords from ?'
select*from @t order by 1

SELECT a3.name [schemaname],a2.name [tablename],a1.rows row_count,
       (a1.reserved+ISNULL(a4.reserved,0))*8 reserved,a1.data*8 data,
(CASE WHEN (a1.used+ISNULL(a4.used,0))>a1.data THEN(a1.used+ISNULL(a4.used,0))-a1.data ELSE 0 END)*8 index_size,
(CASE WHEN (a1.reserved+ISNULL(a4.reserved,0))>a1.used THEN(a1.reserved+ISNULL(a4.reserved,0))-a1.used ELSE 0 END)*8 unused
FROM(SELECT ps.object_id,SUM(CASE WHEN(ps.index_id < 2)THEN row_count ELSE 0 END)[rows],
		SUM(ps.reserved_page_count)reserved,
		SUM(CASE WHEN(ps.index_id<2)THEN(ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)END)data,
		SUM(ps.used_page_count)used
	FROM sys.dm_db_partition_stats ps
    WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized=1)
	GROUP BY ps.object_id)a1
LEFT OUTER JOIN(SELECT it.parent_id,SUM(ps.reserved_page_count)reserved,SUM(ps.used_page_count)used
	 FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	 WHERE it.internal_type IN (202,204)
	 GROUP BY it.parent_id)a4 ON a4.parent_id = a1.object_id
INNER JOIN sys.all_objects a2  ON a1.object_id = a2.object_id
INNER JOIN sys.schemas a3 ON a2.schema_id = a3.schema_id
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name

SELECT a3.name[schemaname],a2.name[tablename],a1.rows row_count FROM
(SELECT ps.object_id,SUM(CASE WHEN(ps.index_id<2)THEN row_count ELSE 0 END)[rows]
from sys.dm_db_partition_stats ps
WHERE ps.object_id NOT IN(SELECT object_id FROM sys.tables WHERE is_memory_optimized=1)
group by ps.object_id)a1 INNER JOIN sys.all_objects a2 ON a1.object_id=a2.object_id
INNER JOIN sys.schemas a3 ON a2.schema_id=a3.schema_id
WHERE a2.type<>N'S'and a2.type<>N'IT'ORDER BY 1,2