Improved version!! I added Schema, since we can have more than 1 table with the same name in the DB.

Note: We don't need the temporary table as a placeholder.

In any case if needed, DO NOT use the "SELECT * into #temp_table1"; For bigger DATASETS, this cmd is BAD; It'll create a lockout in Tempdb.

The best way is to create the place holder via DDL (ie. SELECT * into #temp_table1 where 1 = 2) Once the placeholder is created as #temp_table1

Then issue the insert stmnt :

INSERT #temp_table1

SELECT * FROM WhatEverquery

This will save you lots of headaches

here's the improved version which can also b Parameterized as a Sproc if needed

SELECT s.name +'.' + B.name, CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024)) as [Table Used Size(MB)],

CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) as [Table Located Size(MB)],

(CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) - CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024))) as [Table Free Size(MB)] ,

A.row_count

from sys.dm_db_partition_stats A join sys.all_objects B

ON A.object_id = B.object_id

join sys.schemas S

on B.schema_id = S.schema_id

where B.type != 'S'

group by s.name +'.' + B.name, row_count

order by s.name +'.' + B.name asc