How to get rowcount/size for eachtable.

Many times we want to get number of rows and spaceused by each table.
We can use sp_spaceused for this.
But when we want this info for all tables than we need some simple way.



Here is a solution for this.

create table #tempsize
(table_name varchar(256),
rows int,
reserved varchar(256),
data varchar(256),
index_size varchar(256),
unused varchar(256)
)

insert into #tempsize
exec sp_msforeachtable 'exec sp_spaceused ''?'''


select * from #tempsize
order by rows desc

Since sp_spaceused will give us approximate data. If we need exact number of row count we can try this way

create table #temprowcountforeachtable
(table_name varchar(256),
row_count int)



insert into #temprowcountforeachtable
exec sp_msforeachtable 'select convert (varchar(256),''?'') as table_name, count(*) from ?'


select * from #temprowcountforeachtable order by row_count desc



This will give us exact row count.

Cheers.