Get Record Count for each table in a database.

Question

Post reply

Get Record Count for each table in a database.

Hari.Sharma

SSCertifiable

Points: 6904
More actions
September 28, 2007 at 1:07 am

#181519

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

Viewing 15 posts - 1 through 15 (of 16 total)

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

An alternative without cursor is:

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

Toni

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toniupstny Hall of Fame Points: 3986 More actions · Answer 3

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 SSC Guru Points: 143329 More actions · Answer 4

Now this view can get the estimated row counts (and a lot more besides)without cursors: http://www.sqlservercentral.com/scripts/tables/62545/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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! 😛

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

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 9

rbarryyoung (8/10/2008)
Gimme time to eat lunch, Jeff! 😛 🙂

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff Moden (8/10/2008)
rbarryyoung (8/10/2008)
Gimme time to eat lunch, Jeff! 😛 🙂
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. :sick:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 13

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 14

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

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)