How to get the Record Count of all the tables in a Database

Question

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

'insert into #rowcount select ''?'', count(*) from ?'

select * from #rowcount

order by tablename

drop table #rowcount

CrazyMan SSChampion Points: 12560 More actions · Answer 2

One way u can use sysindexes table to get the row count of all the table on that databases 🙂

steveb. SSC-Forever Points: 46733 More actions · Answer 3

CrazyMan (12/11/2008)
One way u can use sysindexes table to get the row count of all the table on that databases 🙂

This not always accurate, all this has been discussed in depth here

brainy SSCrazy Points: 2445 More actions · Answer 4

select object_name(id), rowcnt from sysindexes where id in (select id from sysobjects where type = 'u') and indid = 0

Use the above code.

CrazyMan SSChampion Points: 12560 More actions · Answer 5

Hi Steve

I do agree, i forget to mention that :), depends upon the usage

brainy SSCrazy Points: 2445 More actions · Answer 6

This not always accurate, all this has been discussed in depth here
http://www.sqlservercentral.com/Forums/Topic604340-149-1.aspx

Yes exactly... the sysindexes might not have correct values in all the cases.

I think you better use the code

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

'insert into #rowcount select ''?'', count(*) from ?'

select * from #rowcount

order by tablename

drop table #rowcount

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

course - if you have any big tables that would take a while.

We use sysindexes - as count(*) over 2 billion records may offend.

Oh and on 2000 at least sp_spaceused seems to break once you hit the int limit on rows - but there is a second column in sysindexes that handles it 🙂

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

Madhivanan

Failing to plan is Planning to fail