How To Get Table Row Counts Quickly And Painlessly

Question

Post reply

How To Get Table Row Counts Quickly And Painlessly

Viewing 15 posts - 76 through 90 (of 108 total)

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

SPK Valued Member Points: 64 More actions · Answer 1

The below query gives the Table name(user table) and the rows in each:

This is faster because we are getting the data from sysindexes.

SELECT object_name(id), rows

FROM sysindexes

WHERE INDID IN (0,1)

AND object_name(id) NOT LIKE 'sys%'

SPK Valued Member Points: 64 More actions · Answer 2

SELECT object_name(id), rows

FROM sysindexes

WHERE INDID IN(0,1)

AND object_name(id) NOT LIKE 'sys%'

Gives all the tables and the rows in each table.

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 3

SPK (9/17/2009)
SELECT object_name(id), rows
FROM sysindexes
WHERE INDID IN(0,1)
AND object_name(id) NOT LIKE 'sys%'
Gives all the tables and the rows in each table.

As has been said before in this thread, that won't be accurate unless you update usage first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

windows_mss SSCrazy Points: 2423 More actions · Answer 4

Hi Kendal Van Dyke,

"How To Get Table Row Counts Quickly And Painlessly" really helpful and crisp snippet to find the rowcount of the table, my doubt while we are using select count(*) from

,in this we can use where clause to find the row count for the particular condition, select count(*) from

where [colname] = 'xyz'

how can i achieve this from your snippet?

once again thanks for your idea 🙂

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

bijayanix24 SSC-Addicted Points: 427 More actions · Answer 5

Sometimes there is a need to get the record count of every table in a SQL Server database. The common method used for achieving this is doing a select count(*) from table_name but this can create a lot of overhead in large tables present in large databases.

The record count of every user table is maintained in the sysindexes system table . There is a indid column in the sysindexes table and every user table will have an entry in sysindexes with indid value of either 0 or 1 , but not both. The rowcnt column corresponding to indid 0 or 1 gives us the total row count of a table.

The User Defined Function given below shows how to retrieve row count for a table by making use of sysindexes.

Eliza

Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire[/url]: India's Only Company to be both Apple Premier & Microsoft Gold certified.

Paul White SSC Guru Points: 150467 More actions · Answer 6

bijayanix24 (2/9/2010)
...

'Team Mindfire' might like to read the other comments in this discussion. Much better solutions exist.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

gulam.husain.ansari SSC Rookie Points: 26 More actions · Answer 7

Can anyone one told me what will be the shortest and fastest way of find the

count of table data

i am using

SELECT COUNT(*) FROM TblEmployee -- i want only count not else

what can i do for that

srikant maurya Hall of Fame Points: 3293 More actions · Answer 8

U can Use below sql query to count the record from any user table

select rows

from sys.sysindexes

where id = object_id('Table_Name')

and indid < 2

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

If you use versions prior to 2005, you need to use dbcc updateusage to get accurate count

http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Madhivanan

Failing to plan is Planning to fail

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

Since SQL 2005 I've tended to use Sys.Partitions over a Select Count(*), especially when I need to get row numbers across all tables in a specified DB and also across numerous DB's.

However, in future I will be adding the code to Update Stats prior to running the process, just to be sure the counts are as acurate as possible, may be with a 10-20 second delay.

My question is, would the Recovery Model for the DB impact the accuracy of the sys.Partitions and sys.dm_db_partition_stats views?

TIA.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

david.wright-948385 SSCarpal Tunnel Points: 4039 More actions · Answer 11

rja.carnegie (9/2/2009)
I find the report of DBCC CHECKDB useful.
Why doesn't "SELECT COUNT(*) FROM table" look at an index, which apparently it doesn't(?)

For 'select count(*) from

' the 2008 optimiser will scan the PK of the table if it has one. Not as good as (some of) the DMV queries, but not as bad as a full table scan.

padmanabanmsc Newbie Points: 5 More actions · Answer 12

Intresting post but not so much useful. Most of the time we need to know the count agaist with some filtered criteria. For me, I never come across the requirment like, show the count of table.

Anyway, good to know the way if required to get the total count of table.

DJLondonCA Newbie Points: 7 More actions · Answer 13

I agree. I too am curious under what circumstances it would be useful to know the row-count from all tables.

Also under what circumstances is it useful to use 6 or 7 lines of query versus a single SELECT statement.

As a peek into the guts of the SQL Server system and thinking behind it, this is fascinating, but seems it would rarely be of use.

Pat Reddy SSC Enthusiast Points: 107 More actions · Answer 14

I agree with previous posters that this seems like a good way to find row counts for all tables in a database, but not a very good (easy, efficient, etc...) alternative to COUNT(*) on a single table within a query. I created the following proc and tested with my largest tables - hundreds of millions of rows with a non-sequential GUID pk and the results were generally 1 sec for COUNT(*) and 0 sec for the proc. Considering how I use COUNT(*) in my logic (and it is rarely used), it doesn't seem worth it - merely a nifty way to complicate things.

CREATE PROC spUtil_GetRowCount

(

@TableName VARCHAR(200)

)

AS

SELECT

ddps.row_count

FROM

sys.indexes i

INNER JOIN sys.objects o

ON i.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats ddps

ON i.OBJECT_ID = ddps.OBJECT_ID

AND i.index_id = ddps.index_id

WHERE

i.index_id < 2

AND o.is_ms_shipped = 0

AND o.name = @TableName

Wanderlei Santos SSC Veteran Points: 221 More actions · Answer 15

Nice article, but for SQL 2005 / 8 / r2, I just right-click and run the table usage report from sms. It also gives space used/free.