SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Record Count for each table in a database.


Get Record Count for each table in a database.

Author
Message
Hari.Sharma
Hari.Sharma
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1618 Visits: 455
Comments posted to this topic are about the item Get Record Count for each table in a database.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

toniupstny
toniupstny
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 940
An alternative without cursor is:

sp_msforeachtable 'select ''?'', count(*) Rows from ?'



Toni
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89439 Visits: 41144
toniupstny (8/10/2008)
An alternative without cursor is:

sp_msforeachtable 'select ''?'', count(*) Rows from ?'



Toni


Ummm.... not quite correct, Toni. If you go and look at the code for sp_MSForEachTable, you'll find a monster cursor that is typically slower than any cursor you would ever intentionally build because it was written to handle just about any size code. The cursor that Hari wrote will likely run much faster than using sp_MSForEachTable.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
toniupstny
toniupstny
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 940
Thanks Jeff.

I was just looking through the code for the sp_MSforeach procedures (generated a Create for sp_MSforeachtable, sp_MSforeachworker and the sp_ MSforeach_worker) to see why the table one couldn't be called from the database one. I saw the cursor like you said right there so was coming back to post a "NEVERMIND"!

Hey... I learned something anyway... thanks again.

Toni
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15306 Visits: 9518
Now this view can get the estimated row counts (and a lot more besides)without cursors: http://www.sqlservercentral.com/scripts/tables/62545/

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89439 Visits: 41144
Aye... that's more like it.

The only thing I'd be worried about is... do you need to update usage (DBCC UPDATEUSAGE) as you do in SQL Server 2000 to get accurate row counts?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8023 Visits: 9971
For SQL Server 2005 and greater - there is no need to cursor at all. Just use the DMV sys.dm_db_partition_stats as in the following:


Select object_schema_name(object_id) As SchemaName
,object_name(object_id) As ObjectName
,row_count
From sys.dm_db_partition_stats
Where index_id < 2
And object_schema_name(object_id) <> 'sys'
Order By object_schema_name(object_id);



Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89439 Visits: 41144
Jeffrey Williams (8/10/2008)
For SQL Server 2005 and greater - there is no need to cursor at all. Just use the DMV sys.dm_db_partition_stats as in the following:


Select object_schema_name(object_id) As SchemaName
,object_name(object_id) As ObjectName
,row_count
From sys.dm_db_partition_stats
Where index_id < 2
And object_schema_name(object_id) <> 'sys'
Order By object_schema_name(object_id);




Again, I ask... do you have to update usage for the rowcounts to be accurate?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15306 Visits: 9518
Jeff Moden (8/10/2008)
Again, I ask... do you have to update usage for the rowcounts to be accurate?


Gimme time to eat lunch, Jeff! Tongue

Yes, you do. But unless it is a table with rapidly shifting contents, you shouldn't have to do it every time. Besides, I couldn't put it in my view anyway. Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89439 Visits: 41144
rbarryyoung (8/10/2008)
Gimme time to eat lunch, Jeff! Tongue Smile


Heh... hey! I need to know... does brother Darth have to take off the Chevy look-alike to eat?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search