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 Sunday, August 10, 2008 11:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Jeff Moden (8/10/2008)
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?


Straws. And shakes. Lots of shakes. Milk shakes, oatmeal shakes, yam shakes, broccoli shakes, steak shakes, spinach shakes, etc.


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

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
Jeff Moden (8/10/2008)
Again, I ask... do you have to update usage for the rowcounts to be accurate?


Well, according to BOL - no, you don't have to update usage.


Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005 and later, these values are always maintained correctly. Databases upgraded from SQL Server 2000 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to correct any invalid counts.


But, as we all know - BOL is not always correct. There are situations where you will need to update usage, but I am not sure what those are. We just went through a large purge operation and these numbers were updated accurately.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #549893
Posted Sunday, August 10, 2008 11:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:23 AM
Points: 242, Visits: 939
Since I went ahead and made the query which actually does provide the table and associated rowsize without cursors thought I would post it. (though I am sure Barry's does a LOT more ).

SELECT so.name 'Table', si.rows 'Rows'
FROM
(SELECT Name FROM sysobjects WHERE type='U') so
JOIN
(SELECT rows,id,indid FROM sysindexes) si
ON si.id = OBJECT_ID(so.name) AND si.indid < 2


Toni
Post #549895
Posted Sunday, August 10, 2008 1:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
So... hedging a bet against BOL and changing the code to something a bit more conventional, we end up with this...

   DBCC UPDATEUSAGE (0)
SELECT so.Name AS TableName,
si.Rows AS [Rows]
FROM sys.SysObjects so
INNER JOIN sys.SysIndexes si
ON si.ID = so.ID
WHERE si.IndID < 2
AND so.XType = 'U'
AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0



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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #549905
Posted Sunday, August 10, 2008 1:48 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
... and, with SQL Server 2000, we end up with this...

   DBCC UPDATEUSAGE (0)
SELECT so.Name AS TableName,
si.Rows AS [Rows]
FROM dbo.SysObjects so
INNER JOIN dbo.SysIndexes si
ON si.ID = so.ID
WHERE si.IndID < 2
AND so.XType = 'U'
AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0

Yeah... I know... if I didn't use the two part naming convention, they'd both work and they'd both be identical. Actually, the code above will work in both environments... I just have an affinity for the two part naming convention for a lot of reasons. :P


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #549908
Posted Sunday, August 10, 2008 2:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Jeffrey Williams (8/10/2008)

Well, according to BOL - no, you don't have to update usage.

Hmm, yeah I see where it says that Jeff. I may be wrong here, when I wrote my view (about 2-3 years ago) I was still working on both 2000 & 2005 most days and I remember having accuracy problems that ultimately caused my to add DBCC UpdateUsage periodically. But it may have been only happening on SQL 2000.


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

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
rbarryyoung (8/10/2008)

Hmm, yeah I see where it says that Jeff. I may be wrong here, when I wrote my view (about 2-3 years ago) I was still working on both 2000 & 2005 most days and I remember having accuracy problems that ultimately caused my to add DBCC UpdateUsage periodically. But it may have been only happening on SQL 2000.


Either way, it doesn't hurt to update usage as long as you have the time. The important thing about any of these techniques (yours, mine and Jeff's) are that they are not cursor based and return the desired results.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #549918
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse