Count number of records in a table

Question

Count number of records in a table

Viewing 9 posts - 16 through 23 (of 23 total)

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

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

That is true, and again you are correct. I just felt that it was worth mentioning. Its an indicator of whether the rows is accurate or not. A proc which checks for a 0 value there before doing an actual count of the rows can save a major amount of processing time and in my experience has been trustworthy. With as impressed with your overall knowledge base that I've seen on the posts, I think I'd be more likely to believe you forgot it.

Oh, and not updates. Only inserts or deletes affect it.

Edited by - scorpion_66 on 12/04/2002 5:39:03 PM

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Cool something else I can add to my personal knowledge. Thanks. ANd I love the idea of check for value of 0 to verify count is accurate. I need to move that concept into some of the extra work I have going. Will also be help since I manually update my stats to get a better picture of when I need to run that.

Cheddar SSC Eights! Points: 886 More actions · Answer 3

Try using exec sp_spaceused 'tbl_TableName'

This returns how many rows and how much space the table takes up and the indices too.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Do you realize that sp_spaceused gets the rows from sysindexes?

If someone needed the sizes and such, I could see them using it, but it's a lot of unneccessary overhead if the size is not wanted.

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 5

In answer to the original question: -

'Also, in EM, when I right-click properties a table, it tells me the number of records in the table. Does anyone know how this number is generated?'

EM uses the stored procedure sp_MStablespace in the master db to get this value. This returns the three values Rows, DataSpaceUsed and IndexSpaceUsed

and internally uses the command: -

SELECT convert(int, rowcnt) FROM dbo.sysindexes WHERE indid < 2 and id = @id to retrive the number of rows.

Interestingly, I tried looking in BOL for 'sp_MStablespace' and got 'No topics found'.

Regards,

Andy Jones

.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

Almost all of the sp_MS-procs are undocumented (all but one actually, of almost 500!). Many of them are not really a part of SQL Server per se, but rather apart of Enterprise Manager, just as you would create procedures for your apps. In order to have a look at them, just run sp_helptext sp_MStablespace in master, they're usually pretty well commented.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 7

Try this undocumented sp.

set nocount on

create table #sp_mstablespace (Rows INT, DataSpaceUsed INT, IndexSpaceUsed INT )

Insert into #sp_mstablespace

Exec sp_mstablespace <YourTableName>

select * from #sp_mstablespace

paul

dj_meier SSCrazy Points: 2732 More actions · Answer 8

Just another comment about sp_spaceused...Even after an "update statistics" the rowcount can still be reported incorrectly in sp_spaceused. You must run a "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" command to correct the number in sysindexes.

I'm not sure if using the "WITH FULLSCAN" option on "update statistics" will ensure the rowcount is reported correctly?

-Dan

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 9

Scorpion_66

SSCertifiable

Points: 7891

December 12, 2002 at 3:19 pm

#444297

With Fullscan does not affect rowcounts.