Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get Record Count for each table in a database. Expand / Collapse
Author
Message
Posted Friday, September 28, 2007 1:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, 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
Post #403906
Posted Sunday, August 10, 2008 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
An alternative without cursor is:

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



Toni
Post #549861
Posted Sunday, August 10, 2008 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549867
Posted Sunday, August 10, 2008 10:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, 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


Post #549872
Posted Sunday, August 10, 2008 10:27 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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."
Post #549876
Posted Sunday, August 10, 2008 10:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549878
Posted Sunday, August 10, 2008 10:43 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #549879
Posted Sunday, August 10, 2008 10:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549884
Posted Sunday, August 10, 2008 11:03 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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! :P

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. :)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #549885
Posted Sunday, August 10, 2008 11:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
rbarryyoung (8/10/2008)
Gimme time to eat lunch, Jeff! :P :)


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549886
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse