SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kendal Van Dyke / How To Get Table Row Counts Quickly And Painlessly / Latest Posts

RE: How To Get Table Row Counts Quickly And Painlessly

Jamie Thomson — Mon, 13 Feb 2012 02:28:02 GMT

Just one thought, if you don't have permission to access the DMVs (which, as I have just found, I do not), the following is a fairly decent substitute:sp_msforeachtable 'select ''?'', count(*) from ?'

RE: How To Get Table Row Counts Quickly And Painlessly

SQLRNNR — Tue, 08 Feb 2011 12:47:05 GMT

[quote][b]jcrawf02 (2/8/2011)[/b][hr][quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr][quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]homebrew01 (2/2/2011)[/b][hr]No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D[/quote]Evil :-D:w00t::hehe:[/quote]I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:[/quote]Where are the cursor or recursive cte methods for this?[/quote]Can't you just count the rows by hand?[/quote]No. We should automate that.

RE: How To Get Table Row Counts Quickly And Painlessly

jcrawf02 — Tue, 08 Feb 2011 12:30:42 GMT

[quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr][quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]homebrew01 (2/2/2011)[/b][hr]No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D[/quote]Evil :-D:w00t::hehe:[/quote]I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:[/quote]Where are the cursor or recursive cte methods for this?[/quote]Can't you just count the rows by hand?

RE: How To Get Table Row Counts Quickly And Painlessly

SQLRNNR — Tue, 08 Feb 2011 12:19:21 GMT

[quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr][quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]homebrew01 (2/2/2011)[/b][hr]No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D[/quote]Evil :-D:w00t::hehe:[/quote]I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:[/quote]Where are the cursor or recursive cte methods for this?

RE: How To Get Table Row Counts Quickly And Painlessly

Ninja's_RGR'us — Tue, 08 Feb 2011 12:01:32 GMT

[quote][b]CirquedeSQLeil (2/8/2011)[/b][hr][quote][b]homebrew01 (2/2/2011)[/b][hr]No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D[/quote]Evil :-D:w00t::hehe:[/quote]I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:

RE: How To Get Table Row Counts Quickly And Painlessly

SQLRNNR — Tue, 08 Feb 2011 11:59:29 GMT

[quote][b]homebrew01 (2/2/2011)[/b][hr]No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D[/quote]Evil :-D:w00t::hehe:

RE: How To Get Table Row Counts Quickly And Painlessly

homebrew01 — Wed, 02 Feb 2011 09:41:35 GMT

No one has mentioned this method:select * from tableAthen scroll down to the bottom to see how many rows there are:Whistling::w00t::hehe::-D

RE: How To Get Table Row Counts Quickly And Painlessly

alen teplitsky — Wed, 02 Feb 2011 07:22:55 GMT

[quote][b]dmigo (2/1/2011)[/b][hr]This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.So how does SSMS get it?Like this: (edited out of a Profiler trace)select tbl.name AS [Name],SCHEMA_NAME(tbl.schema_id) AS [Schema],ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]FROMsys.tables AS tbl[/quote]how long does this take to run compared to select count(*)?we have some daily reports that give us the row counts in publishers and subscribers that take a long time to run sometimes on tables with tens of millions of rows

RE: How To Get Table Row Counts Quickly And Painlessly

dmigo — Tue, 01 Feb 2011 17:31:14 GMT

This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.So how does SSMS get it?Like this: (edited out of a Profiler trace)select tbl.name AS [Name],SCHEMA_NAME(tbl.schema_id) AS [Schema],ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]FROMsys.tables AS tbl

RE: How To Get Table Row Counts Quickly And Painlessly

rsingh.sql — Tue, 01 Feb 2011 08:15:34 GMT

Interesting article Kendal, I have been using the same dmv as you have mentioned here but with a slight modification and here it is Select Object_Name(ddps.object_id) TableName, ddps.row_count #RowsFrom sys.dm_db_partition_stats ddpsWhere ObjectProperty(ddps.object_id, 'IsUserTable') = 1And ddps.index_id < 2Order By Object_Name(ddps.object_id)

RE: How To Get Table Row Counts Quickly And Painlessly

geert.bens — Tue, 01 Feb 2011 03:49:39 GMT

I'm not sure if this is interesting for programmers, for DBA's sure but programmers are likely to have limited permissions. It is likely they do not have rights to interrogate systemtable or DMV's.just a remark :)

RE: How To Get Table Row Counts Quickly And Painlessly

alen teplitsky — Mon, 31 Jan 2011 08:21:20 GMT

for myself i use the index scanning DMV and just look at the leaf level for the row count

RE: How To Get Table Row Counts Quickly And Painlessly

nigel.meakins — Sat, 29 Jan 2011 07:49:43 GMT

http://www.sqlservercentral.com/Forums/Topic895882-146-1.aspxThis article states that sysindexes gives an approximate value, which is as documented with BOL. The ssms reports also use this, which can get very different results to the actuals. simply verifying that your approach gives the same result as select count(*) for a single db is hardly good enough testing to suggest we all use this approach. You might want to check your own forums before publishing and misleading, and also msdn docs.

RE: How To Get Table Row Counts Quickly And Painlessly

Rahul The Dba — Fri, 28 Jan 2011 12:08:26 GMT

nice article but i must say nothing innovative about it ,,,,,,,,many sql server user's already knows it,,,,,, as i said many not all so nice n easy article :-P

RE: How To Get Table Row Counts Quickly And Painlessly

Pat Reddy — Fri, 28 Jan 2011 09:09:40 GMT

To the last 2 posters:1) sysindexes will not be supported in future releases.2) This was suggested as a quick and easy way to report the # of rows in [u]every[/u] table in a database. Right-clickig every table isn't practical.I think we've beat this horse to death, haven't we?[b]Main points made:[/b] It's a nice artical if you want to learn more about DMVs or are interested in the number of rows in every table in your database, but there are other options for getting at the # of rows in a particular table that are considrably simpler.:-)

RE: How To Get Table Row Counts Quickly And Painlessly

gregg_dn — Fri, 28 Jan 2011 09:04:59 GMT

Enjoyed the article and the comments.

RE: How To Get Table Row Counts Quickly And Painlessly

jenolan — Fri, 28 Jan 2011 08:47:02 GMT

:-) Hi,How about just right clicking a DB table and select properties, then under Select a page, choose Storage and on the right side of the screen you will see Row count. This also shows data space and index space.FYI.

RE: How To Get Table Row Counts Quickly And Painlessly

SanjayAttray — Fri, 28 Jan 2011 08:26:21 GMT

RE: How To Get Table Row Counts Quickly And Painlessly

Wanderlei Santos — Fri, 28 Jan 2011 08:21:45 GMT

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.

RE: How To Get Table Row Counts Quickly And Painlessly

Pat Reddy — Fri, 28 Jan 2011 07:46:16 GMT

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))ASSELECT ddps.row_countFROM 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_idWHERE i.index_id < 2 AND o.is_ms_shipped = 0 AND o.name = @TableName

RE: How To Get Table Row Counts Quickly And Painlessly

DJLondonCA — Fri, 28 Jan 2011 06:59:24 GMT

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.

RE: How To Get Table Row Counts Quickly And Painlessly

padmanabanmsc — Fri, 28 Jan 2011 06:14:47 GMT

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.

RE: How To Get Table Row Counts Quickly And Painlessly

david.wright-948385 — Fri, 28 Jan 2011 02:48:03 GMT

[quote][b]rja.carnegie (9/2/2009)[/b][hr]I find the report of DBCC CHECKDB useful.Why doesn't "SELECT COUNT(*) FROM table" look at an index, which apparently it doesn't(?)[/quote]For '[b]select count(*) from [table][/b]' 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.

RE: How To Get Table Row Counts Quickly And Painlessly

Jason-299789 — Fri, 28 Jan 2011 02:22:19 GMT

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.

RE: How To Get Table Row Counts Quickly And Painlessly

Madhivanan-208264 — Fri, 28 Jan 2011 00:07:34 GMT

If you use versions prior to 2005, you need to use dbcc updateusage to get accurate count[url]http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx[/url]

RE: How To Get Table Row Counts Quickly And Painlessly

srikant maurya — Thu, 27 Jan 2011 22:56:03 GMT

U can Use below sql query to count the record from any user table select rowsfrom sys.sysindexeswhere id = object_id('Table_Name') and indid < 2

RE: How To Get Table Row Counts Quickly And Painlessly

gulam.husain.ansari — Thu, 27 Jan 2011 22:38:38 GMT

Can anyone one told me what will be the shortest and fastest way of find thecount of table datai am using SELECT COUNT(*) FROM TblEmployee -- i want only count not elsewhat can i do for that

RE: How To Get Table Row Counts Quickly And Painlessly

Paul White — Tue, 09 Feb 2010 03:51:51 GMT

[quote][b]bijayanix24 (2/9/2010)[/b][hr]...[/quote]'Team Mindfire' might like to read the other comments in this discussion. Much better solutions exist.

RE: How To Get Table Row Counts Quickly And Painlessly

bijayanix24 — Tue, 09 Feb 2010 02:46:14 GMT

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.[url=http://www.mindfiresolutions.com/Get-Record-Count-of-a-table-in-SQL-Server-without-doing-a-Table-Scan-162.php]Eliza[/url]

RE: How To Get Table Row Counts Quickly And Painlessly

windows_mss — Mon, 16 Nov 2009 04:52:39 GMT

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 [b]select count(*) from [table][/b],in this we can use where clause to find the row count for the particular condition, [b]select count(*) from [table] where [colname] = 'xyz'[/b]how can i achieve this from your snippet? once again thanks for your idea :-)

RE: How To Get Table Row Counts Quickly And Painlessly

Jeff Moden — Thu, 17 Sep 2009 20:16:39 GMT

[quote][b]SPK (9/17/2009)[/b][hr]SELECT object_name(id), rowsFROM sysindexesWHERE INDID IN(0,1)AND object_name(id) NOT LIKE 'sys%'Gives all the tables and the rows in each table.[/quote]As has been said before in this thread, that won't be accurate unless you update usage first.

RE: How To Get Table Row Counts Quickly And Painlessly

SPK — Thu, 17 Sep 2009 12:56:18 GMT

SELECT object_name(id), rowsFROM sysindexesWHERE INDID IN(0,1)AND object_name(id) NOT LIKE 'sys%'Gives all the tables and the rows in each table.

RE: How To Get Table Row Counts Quickly And Painlessly

SPK — Thu, 17 Sep 2009 12:52:27 GMT

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), rowsFROM sysindexesWHERE INDID IN (0,1)AND object_name(id) NOT LIKE 'sys%'

RE: How To Get Table Row Counts Quickly And Painlessly

rja.carnegie — Mon, 07 Sep 2009 02:53:51 GMT

[quote][b]Sanjay Rohra (9/4/2009)[/b][hr]I believe we can use the procs sp_spaceused and sp_statistics for temporary tables by changing the context to "tempdb" database. But that can be done only in the session in which the temporary table has been created :-)[/quote]DBCC CHECKDB(N'tempdb') :-)Unless you can decode the "true object name" of temporary objects ("#___(......)_000000004F5B") to identify the owner - which I think really can bedone only by the owner on a per-object level (but all of mine will be named "...4F5B" apparently) - the limitation you mention still stands. But this also is still the situation - non-global temporary objects specifically - where keeping track of each @@ROWCOUNT [i]may[/i] be sufficient. But also may create the need to do, for instance,SELECT @returnerror = @@ERROR, @rows = @rows + @@ROWCOUNT- because you can't get those one at a time.

RE: How To Get Table Row Counts Quickly And Painlessly

timothyawiseman — Sat, 05 Sep 2009 14:21:05 GMT

Paul White, you made some excellent clarifications. Thank you for expanding on what I was trying to say.

RE: How To Get Table Row Counts Quickly And Painlessly

TheSQLGuru — Fri, 04 Sep 2009 15:25:39 GMT

[quote][b]SQLRocker (9/4/2009)[/b][hr]SSMS 2008 does a good job in displaying the row count next to the table itself.[/quote]DBArtisan has been doing this for about a decade now, but it is the same "not guaranteed to be precise" value that SSMS uses.

RE: How To Get Table Row Counts Quickly And Painlessly

SQLRocker — Fri, 04 Sep 2009 14:28:28 GMT

SSMS 2008 does a good job in displaying the row count next to the table itself.

RE: How To Get Table Row Counts Quickly And Painlessly

TheSQLGuru — Fri, 04 Sep 2009 11:56:55 GMT

[quote][b]Paul White (9/4/2009)[/b][hr]I think Peter may be working his way through from post #1!:laugh:edit: "ORDER BY 1" :sick:[/quote]HAH!! :cool:

RE: How To Get Table Row Counts Quickly And Painlessly

Paul White — Fri, 04 Sep 2009 11:11:02 GMT

I think Peter may be working his way through from post #1!:laugh:edit: "ORDER BY 1" :sick:

RE: How To Get Table Row Counts Quickly And Painlessly

Peter Petrov — Fri, 04 Sep 2009 11:00:58 GMT

[quote][b]jcrawf02 (9/2/2009)[/b][hr]"Quickly and Painlessly" doesn't seem to fit the method, when SELECT COUNT(*) FROM myTable is just four little words (ok, a function ain't a word, whatever) long, and the alternative is joining three tables together and using a WHERE clause. Point taken that the system objects hold this data, but not sure that's easier.[/quote]We don't virtually need sys.indexes and sys.objects tables. ;-)[code="sql"][/code]SELECT OBJECT_NAME(object_id) AS table_name, SUM(row_count) AS row_countFROM sys.dm_db_partition_stats WHERE index_id < 2 AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0 GROUP BY object_idORDER BY 1;[code="sql"][/code]