How To Get Table Row Counts Quickly And Painlessly

Question

How To Get Table Row Counts Quickly And Painlessly

Viewing 4 posts - 106 through 108 (of 108 total)

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

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::w00t::hehe::-D
Evil :-D:w00t::hehe:
I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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."

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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::w00t::hehe::-D
Evil :-D:w00t::hehe:
I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

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

gunjeet_singh Valued Member Points: 54 More actions · Answer 4

Here's a very simple way using sp_spaceused function

declare @tblname nvarchar(100)

Select @tblname = 'yyyyyyyyyy' -- put your table name here

declare @test-2 table(name varchar(100),numrows bigint, reserved nvarchar(1000),data nvarchar(1000),index_size nvarchar(1000),unused nvarchar(1000))

Insert into @test-2 exec sp_spaceused @tblname

Select numrows from @test-2

This gives the results within seconds for even very big tables (>100GB size)