order by cat_name,sch_name,tab_name

use  [YourDatabase];
select 
		a1.object_id
,		a2.name
,       a1.used_page_count * 8 as total_used_pages
,       cast(a1.used_page_count * 8 /1000  as varchar(10)) + 'MB' as SIZE_total_used_pages
,       a1.reserved_page_count * 8 as total_reserved_pages
,       a1.row_count 
from sys.dm_db_partition_stats a1 
inner join sys.all_objects a2  on ( a1.object_id = a2.object_id ) 
left outer join  sys.indexes a3  on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) ) 
where (select count(distinct partition_number) 
           from sys.dm_db_partition_stats a4 
           where (a4.object_id = a1.object_id)) >= 1  and a2.type <>'S'
--and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space
order by a1.used_page_count  desc, a2.name asc, a1.index_id,  a1.partition_number 

Msg 170, Level 15, State 1, Procedure proc_records_per_database, Line 43
Line 43: Incorrect syntax near ' '.

	create table #temp_table_list
	(
		rec_id int identity(1, 1) not null,
		cat_name sysname not null,
		sch_name sysname not null,
		tab_name sysname not null,
		row_count bigint not null default 0,
		reserved_pages bigint not null default 0,
		reserved bigint not null default 0,
		pages bigint not null default 0,
		data bigint not null default 0,
		used_pages bigint not null default 0,    
		used bigint not null default 0,
		index_size bigint not null default 0,
		not_used bigint not null default 0,
		primary key(rec_id)
	)