Query table status (num of rows, total size, used_size, etc)

select p.*, a.* from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
and p.object_id=object_id('Person.Password');

select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number;


select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by total_pages_mb desc;
-- order by p.rows desc;