How To Get Table Row Counts Quickly And Painlessly

Question

How To Get Table Row Counts Quickly And Painlessly

Viewing 6 posts - 106 through 111 (of 111 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: 281344 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)

Srinivas Merugu SSC Enthusiast Points: 176 More actions · Answer 5

Find below script will help to get table row count from database.

create table #rowcount

(

DB varchar(100),

Schemaname varchar(50),

TableName varchar(150),

Row_Count int,

RunDate Datetime

)

ALTER TABLE #rowcount ADD CONSTRAINT cDateRun DEFAULT GETDATE() FOR RunDate

exec sp_MSforeachdb

'

use [?]

IF DB_NAME() not in (''master'', ''model'', ''msdb'', ''tempdb'', ''dbtablebackup'') and DB_NAME() not like ''%_SECURITY%'' and DB_NAME() not like ''dbadmin%''

BEGIN

insert into #rowcount (DB, Schemaname, TableName, Row_Count)

SELECT DB_NAME() as DB, s.name [Schema], o.name [Table],ddps.row_count

FROM sys.indexes AS i

INNER JOIN sys.tables AS o ON i.object_id = o.object_id INNER JOIN sys.dm_db_partition_stats AS ddps ON (i.object_id = ddps.object_id AND i.index_id = ddps.index_id )

INNER JOIN sys.schemas as s on (o.schema_id = s.schema_id)

WHERE i.index_id < 2 AND o.is_ms_shipped = 0

order by 3 desc

END

'

select * from #rowcount order by 1,2,3

drop table #rowcount

Srinivas Merugu SSC Enthusiast Points: 176 More actions · Answer 6