How to get Table row count?

Question

How to get Table row count?

zeo

SSC Eights!

Points: 914
More actions
June 2, 2006 at 9:00 am

#172878

How can I get a table row count from sqlserver besides the following method?
select count(*) from tablename
In Oracle after I gather stats for a table I can use the following query.
select TABLE_NAME, num_rows from user_tables where table_name='X';
Is there a dictionary view that I can query from to get the row count for a table?
We are going to do an upgrade and I wanted to get an easy method to capture the row count before and after the upgrade to ensure they are the same.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

SELECT count(*) is the best way to get current row count in SQL 2000. SQL 2005 has dynamic dictionary views like Oracle.

Greg

zeo SSC Eights! Points: 914 More actions · Answer 2

Thanks, this I understand I was hoping for a magic SQL summary statement for the database rather than running 684 selects

Ballack Cheng Soo How Old Hand Points: 362 More actions · Answer 3

Hi all,

How about select count(*) for the row(s) that created on that date, after that date or before that date?

thanks in advance!

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 4

Hi,

For your previous question, you can use sysindexes table also, inorder to count each rows in the table, directly we can read the no of records deails from this table.

Eg.

SELECT rowcnt from sysindexes WHERE name='sysusers'

Please correct me if iam wrong.

warmest

Jayakumar K.

Thanks
Jay
http://www.sqldbops.com

J. den Boer Grasshopper Points: 13 More actions · Answer 5

When using the sysindexes for information use the dbcc updateusage with count_rows before the query.

The information in sysindexes can be way off. It happened to me. Tables filled with millions of reconds, but in sysindexes only a couple or none for the rowcnt.

<<< Information from the manual >>>

DBCC UPDATEUSAGE

Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

Syntax

DBCC UPDATEUSAGE

( { 'database_name' | 0 }

[ , { 'table_name' | 'view_name' }

[ , { index_id | 'index_name' } ] ]

)

[ WITH [ COUNT_ROWS ] [ , NO_INFOMSGS ]

]

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 6

Try this:

http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm

Thanks...Chris

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

Ballack,

You're talking about something that you can only get if you audit DML statements on a table or if you use a 3rd party transaction log reader, like Log Explorer. There's nothing built in to SQL Server that tracks update history.

Greg

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 8

This should help you.

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('<TABLE_NAME>') AND indid < 2

------------
Prakash Sawant

http://psawant.blogspot.com

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 9

And if you don't want to play with system tables then just run the sp_spaceused YourTable system stored proc to get an approximative count. if you run dbcc updateusage before and you dont have and insert and delete in the mean time then the result will be accurate.

But sp_spaceused gives you a good estimate.

Anyhow if you need and exact row count there is only select count(*) as the only way.

Bye
Gabor