All Table sizes and row counts

 

 

create table #tmp1 (

      Servername SysName,

      DBName SysName,

      object_id int,

      ScheamaName sysName,

      TableName sysName,

      TableSize_KB int,

      row_count int

)

 

create table #tmp2 (

      Servername SysName,

      DBName SysName,

      object_id int,

      ScheamaName sysName,

      TableName sysName,

      row_count int

)

 

exec sp_msforeachdb 'use ?;

if DB_ID()>4 begin

      insert #tmp1

      select @@ServerName,DB_Name(),ps.object_id,

                  schema_name(o.schema_id) as SchemaName,

                  o.name as TableName,

                  SUM(ps.reserved_page_count)*8 as TableSize_KB,

                  0 as row_count

      from sys.dm_db_partition_stats ps

      join sys.objects o on o.object_id = ps.object_id

      where o.type=''U''

      group by ps.object_id,o.schema_id, o.name

 

      insert #tmp2

      select @@ServerName,DB_Name(),ps.object_id,

                  schema_name(o.schema_id) as SchemaName,

                  o.name as TableName,   

                   sum(ps.row_count) as row_count

      from sys.dm_db_partition_stats ps

      join sys.objects o on o.object_id = ps.object_id

      where o.type=''U'' and ps.index_id in (0,1)

      group by ps.object_id,o.schema_id, o.name

end

';

 

 

update t1

set t1.row_count=t2.row_count

from #tmp1 t1

left join #tmp2 t2 on

      t2.ServerName=t1.ServerName and

      t2.DBName=t1.DBName and

      t2.object_id=t1.object_id

 

 

select * from #tmp1

order by TableSize_KB desc

 

drop table #tmp1

drop table #tmp2