to display no. rows

Question

Post reply

to display no. rows

bala2

SSCertifiable

Points: 5547
More actions
June 2, 2010 at 7:47 pm

#221698

to display number of rows in each table for a db?

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ben Teraberry SSChampion Points: 11815 More actions · Answer 1

Google "sql number of rows in each table" and win.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95405 More actions · Answer 2

SELECT TableName = st.name, [# Rows] = sip.rows

FROM sys.system_internals_partitions sip

JOIN sys.tables st

ON st.object_id = sip.object_id

WHERE st.is_ms_shipped = 0

ORDER BY TableName

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 3

Try this

SELECT id, so.name, Rows FROM dbo.sysindexes si

INNER JOIN sys.objects so

ON so.[object_id] = si.ID

WHERE si.indid < 2 and so.Type = 'U'

ORDER BY 2

Hay WayneS let me know if it is not good way.

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Paul White SSC Guru Points: 150457 More actions · Answer 4

One more:

SELECT Derived.table_name,

row_count = SUM(P.[rows])

FROM sys.tables T

JOIN sys.partitions P

ON P.[object_id] = T.[object_id]

CROSS

APPLY (

SELECT QUOTENAME(SCHEMA_NAME(T.[schema_id])) + N'.' + QUOTENAME(T.name)

) Derived (table_name)

WHERE T.type_desc = N'USER_TABLE'

AND T.is_ms_shipped = 0

AND P.index_id IN (0, 1)

GROUP BY Derived.table_name

ORDER BY row_count DESC;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

One more...

You can execute this in the DB where you want to check

execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'

Thanks & Regards,
MC

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 6

only4mithunc (6/3/2010)
One more...
You can execute this in the DB where you want to check
execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'

You could do that, but it's horribly slow... the sysindexes query is much much quicker!

I ran the following query against an 18 row table (not really called tablenamehere):

select count(*) from tablenamehere

SELECT [rows]

FROM sysindexes

WHERE

id = object_id('tablenamehere')

AND indid IN (0,1)

The first query (select count(*)...) was responsible for 92% of the query cost.

Thomas Rushton
blog: https://thelonedba.wordpress.com

steveb. SSC-Forever Points: 46733 More actions · Answer 7

ThomasRushton (6/3/2010)
only4mithunc (6/3/2010)
One more...
You can execute this in the DB where you want to check
execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'
You could do that, but it's horribly slow... the sysindexes query is much much quicker!

Performance comes with the (small) cost of accuracy though

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 8

steveb. (6/3/2010)
ThomasRushton (6/3/2010)
only4mithunc (6/3/2010)
One more...
You can execute this in the DB where you want to check
execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'
You could do that, but it's horribly slow... the sysindexes query is much much quicker!
Performance comes with the (small) cost of accuracy though

Given the original question, though, I think that was a reasonable assumption... 😉

Thomas Rushton
blog: https://thelonedba.wordpress.com

steveb. SSC-Forever Points: 46733 More actions · Answer 9

ThomasRushton (6/3/2010)
steveb. (6/3/2010)
ThomasRushton (6/3/2010)
only4mithunc (6/3/2010)
One more...
You can execute this in the DB where you want to check
execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'
You could do that, but it's horribly slow... the sysindexes query is much much quicker!
Performance comes with the (small) cost of accuracy though
Given the original question, though, I think that was a reasonable assumption... 😉

well you know what they say about assumptions;-)

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 10

steveb. (6/3/2010)
ThomasRushton (6/3/2010)
steveb. (6/3/2010)
ThomasRushton (6/3/2010)
only4mithunc (6/3/2010)
One more...
You can execute this in the DB where you want to check
execute sp_MSforeachtable @command1= 'select ''?'' TableName,count(*) NumberOfRecords from ?'
You could do that, but it's horribly slow... the sysindexes query is much much quicker!
Performance comes with the (small) cost of accuracy though
Given the original question, though, I think that was a reasonable assumption... 😉
well you know what they say about assumptions;-)

That's one to you.

So, to be serious for a moment (I know, it's not normal)... what's the problem with the sysindexes query?

Thomas Rushton
blog: https://thelonedba.wordpress.com

Paul White SSC Guru Points: 150457 More actions · Answer 11

No real problem - the row count just isn't guaranteed to be absolutely spot on.

Oh and sysindexes is deprecated.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Two things

1) It's not guaranteed to be correct.

2) sysindexes is a deprecated system view, included only for backward compatability with SQL 2000 and will be removed from a future version.

On SQL 2005/2008, rather query sys.partitions for row counts if you don't want to actually query the table. Still not 100% certain to be 100% accurate 100% of the time, but close.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 13

Sysindexes is deprecated - now that's a good reason.

Thomas Rushton
blog: https://thelonedba.wordpress.com