To get the information about how much space or size used by tables. You can retrieve the size information by linking multiple tables in sys tables. There are two tables that hold this information. The first one is the sys.partitions table. You can get the no of rows created of a table. The second will be the sys.allocation_units table which will return the information of used_pages info.

Here is the TSQL query to return the table information with rows records and space used in SQL Server.

SELECT  t.name, 
		t.type_desc, 
		p.rows,
		CONCAT(CONVERT(NVARCHAR(20), SUM(CONVERT(decimal, (a.used_pages) * 8) / 1024)), ' MB') AS table_size, 
		t.create_date, 
		t.modify_date
FROM sys.Tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.name, 
		 t.type_desc, 
		 p.rows,
		 t.create_date, 
		 t.modify_date
ORDER BY name