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 Wednesday, February 02, 2011 7:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
dmigo (2/1/2011)
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


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


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 #1057456
Posted Wednesday, February 02, 2011 9:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 2,795, Visits: 8,298
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are




Post #1057566
Posted Tuesday, February 08, 2011 11:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
homebrew01 (2/2/2011)
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are



Evil




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1060522
Posted Tuesday, February 08, 2011 12:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are



Evil



I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster
Post #1060527
Posted Tuesday, February 08, 2011 12:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
Ninja's_RGR'us (2/8/2011)
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are



Evil



I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster


Where are the cursor or recursive cte methods for this?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1060555
Posted Tuesday, February 08, 2011 12:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 2,628, Visits: 19,098
CirquedeSQLeil (2/8/2011)
Ninja's_RGR'us (2/8/2011)
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are



Evil



I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster


Where are the cursor or recursive cte methods for this?
Can't you just count the rows by hand?


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #1060564
Posted Tuesday, February 08, 2011 12:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
jcrawf02 (2/8/2011)
CirquedeSQLeil (2/8/2011)
Ninja's_RGR'us (2/8/2011)
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:

select * from tableA

then scroll down to the bottom to see how many rows there are



Evil



I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster


Where are the cursor or recursive cte methods for this?
Can't you just count the rows by hand?



No. We should automate that.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1060578
Posted Monday, February 13, 2012 2:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, Visits: 188
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 ?'


Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #1251053
Posted Thursday, November 21, 2013 7:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 7:05 PM
Points: 2, Visits: 32
Here's a very simple way using sp_spaceused function

declare @tblname nvarchar(100)
Select @tblname = 'yyyyyyyyyy' -- put your table name here
declare @test table(name varchar(100),numrows bigint, reserved nvarchar(1000),data nvarchar(1000),index_size nvarchar(1000),unused nvarchar(1000))
Insert into @test exec sp_spaceused @tblname

Select numrows from @test

This gives the results within seconds for even very big tables (>100GB size)
Post #1516626
« Prev Topic | Next Topic »

Add to briefcase «««7891011

Permissions Expand / Collapse