Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

How To Get Table Row Counts Quickly And Painlessly Expand / Collapse
Author
Message
Posted Friday, January 28, 2011 8:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
.

SQL DBA.
Post #1055347
Posted Friday, January 28, 2011 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 19, 2011 7:46 AM
Points: 1, Visits: 26
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.
Post #1055364
Posted Friday, January 28, 2011 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 226, Visits: 496
Enjoyed the article and the comments.
Post #1055384
Posted Friday, January 28, 2011 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 6:39 PM
Points: 7, Visits: 165
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 every table in a database. Right-clickig every table isn't practical.

I think we've beat this horse to death, haven't we?
Main points made: 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.

Post #1055389
Posted Friday, January 28, 2011 12:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:59 AM
Points: 127, Visits: 133
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


Rahul
Post #1055528
Posted Saturday, January 29, 2011 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 3:45 AM
Points: 6, Visits: 67
http://www.sqlservercentral.com/Forums/Topic895882-146-1.aspx

This 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.
Post #1055767
Posted Monday, January 31, 2011 8:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
for myself i use the index scanning DMV and just look at the leaf level for the row count

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1056220
Posted Tuesday, February 1, 2011 3:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 3:49 AM
Points: 10, Visits: 33
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 :)
Post #1056699
Posted Tuesday, February 1, 2011 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:18 PM
Points: 249, Visits: 172
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 #Rows
From sys.dm_db_partition_stats ddps
Where ObjectProperty(ddps.object_id, 'IsUserTable') = 1
And ddps.index_id < 2
Order By Object_Name(ddps.object_id)
Post #1056847
Posted Tuesday, February 1, 2011 5:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 2, Visits: 432
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]
FROM
sys.tables AS tbl



Post #1057140
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse