How To Get Table Row Counts Quickly And Painlessly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kendal Van Dyke » How To Get Table Row Counts Quickly And...

108 posts, Page 11 of 11

«««7 8 9 10 11

How To Get Table Row Counts Quickly And Painlessly

Rate Topic

Display Mode

Topic Options

Author

Message

alen teplitsky

Posted Wednesday, February 02, 2011 7:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 10:43 AM
Points: 1,411, Visits: 4,517

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

homebrew01

Posted Wednesday, February 02, 2011 9:41 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 2,581, Visits: 7,295

No one has mentioned this method:

select * from tableA

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

Whistling

Post #1057566

SQLRNNR

Posted Tuesday, February 08, 2011 11:59 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 18,858, Visits: 12,443

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

Whistling

Evil

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

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

Ninja's_RGR'us

Posted Tuesday, February 08, 2011 12:01 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 21,376, Visits: 9,584

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

Whistling

Evil

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

Post #1060527

SQLRNNR

Posted Tuesday, February 08, 2011 12:19 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 18,858, Visits: 12,443

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

Whistling

Evil

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

Where are the cursor or recursive cte methods for this?

Post #1060555

jcrawf02

Posted Tuesday, February 08, 2011 12:30 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:46 PM
Points: 2,561, Visits: 18,910

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

Whistling

Evil

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

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

SQLRNNR

Posted Tuesday, February 08, 2011 12:47 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 18,858, Visits: 12,443

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

Whistling

Evil

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

Where are the cursor or recursive cte methods for this?

Can't you just count the rows by hand?

No. We should automate that.

Post #1060578

Jamie Thomson

Posted Monday, February 13, 2012 2:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:03 AM
Points: 877, Visits: 185

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

« Prev Topic | Next Topic »

108 posts, Page 11 of 11

«««7 8 9 10 11

Permissions