Use sp_MSforeachtable to gen COUNT(*) for each table in MyDBname

Question

Post reply

Use sp_MSforeachtable to gen COUNT(*) for each table in MyDBname

Express12

SSCoach

Points: 18514
More actions
April 6, 2006 at 8:20 am

#112479

I'm trying to dynamically COUNT the # rows per table in a DB using sp_MSforeachtable. The following syntax executes DBCC CHECKTABLE for each table in my DB.
HOW can I modify the following SQL to perform a SELECT COUNT(*) from each table in my DB?
USE MyDBname
GO
sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"
/* NOTE:You can issue up to three commands to the stored procedure using @command1 through @command3. */

BT

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Bill,

Can you try this one

sp_MSforeachtable @command1="select count(*) from ?"

Thanks and have a great day!!!

Lucky

Express12 SSCoach Points: 18514 More actions · Answer 2

thx for the reply. Unfortunately, the solution is not returning the "table name" -- ONLY the rowcounts. Is there a way to modify this statement to return the "table name" AND corresponding rowcount?

sp_MSforeachtable @command1="select count(*) from ?"

BT

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 3

I don't think that it is possible with sp_MSforeachtable, but why don't use systems table, eg.:

SELECT object_name(id), rows

FROM sysindexes

WHERE indid IN (0, 1)

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Hi,

if you search this site with the keywords Table Rowcount you will find a lot of discussions with queries and some scripts. I have the following one for rowcont with table names and sp_MSforeachtable, I think from this site too, but not really sure. If somebody recognizes his/her ownership for the following query, please, let us know:

set nocount on

create table #reccount (tbl_name varchar(100), rec_count int)

exec sp_MSforeachtable 'insert #reccount select ''?'',count(*) rec_count from ?'

select * from #reccount order by tbl_name

Regards,Yelena Varsha

Lowell SSC Guru Points: 323518 More actions · Answer 5

how about this?

sp_MSforeachtable @command1="select '?' AS TABLE_NAME,count(*) from ?"

results:

TABLE_NAME

---------------- -----------

[dbo].[SFPORGLM] 0

TABLE_NAME

--------------- -----------

[dbo].[GMHOME1] 426

etc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Senino SSC Journeyman Points: 91 More actions · Answer 6

You can use this as well:

sp_MSforeachtable @command1 ="print 'TABLE NAME: ' + '?' SELECT COUNT(*) FROM ?"

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

dobrzak (4/6/2006)
I don't think that it is possible with sp_MSforeachtable, but why don't use systems table, eg.:
SELECT object_name(id), rows
FROM sysindexes
WHERE indid IN (0, 1)

If you really want to keep track of things like number of rows in tables and how many rows you've inserted since the last time statistics were updated, etc, etc... you need this baby...

DBCC UPDATEUSAGE ('Belution') WITH COUNT_ROWS

Combine that with Dobrzak's code above and you've got something that beats the tar out of sp_MsForEachTable which is really a cursor on steroids.

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

Russel Bell Old Hand Points: 305 More actions · Answer 8

/* Run this as text, capturing the output as text instead of grid and run it */

/* You can modify the SELECT statement at the bottom to filter & order */

/* INFORMATION_SCHEMA views contain schema info without directly referencing system tables that may change */

/* SELECT * FROM [INFORMATION_SCHEMA].TABLES */

set quoted_identifier OFF

SET NOCOUNT ON

PRINT'DECLARE @MyTableVar table(

tbl VARCHAR(50),

numrows int);'

select "INSERT INTO @MyTableVar SELECT '" + TABLE_SCHEMA + '.' + TABLE_NAME +

"', count(*) AS NumRows FROM [" + TABLE_NAME + "];"

from [INFORMATION_SCHEMA].TABLES where TABLE_TYPE = 'BASE TABLE'

order by TABLE_SCHEMA, TABLE_NAME

PRINT 'SELECT * FROM @MyTableVar;'

OptimalDevelopment Old Hand Points: 399 More actions · Answer 9

Try this:

[font="Courier New"]

USE *your_db_name_here*;

GO

sp_MSforeachtable 'select ''?'' Tablename, count(*) ''Rows'' from ?';

[/font]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 10

... and yet another way to skin the cat ...

sp_MSforeachtable @command1="select count(*) as '?' from ?"

... granted not efficient, nor possibly accurate ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

OptimalDevelopment Old Hand Points: 399 More actions · Answer 11

rudy komacsar (7/17/2009)
... and yet another way to skin the cat ...
sp_MSforeachtable @command1="select count(*) as '?' from ?"
... granted not efficient, nor possibly accurate ...

Nice! Like the column name as the table. 🙂

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 12

The following takes about the same time as the sp_MSForEachTable and has the distinct advantage of returning the row counts as a sortable single result set instead of a result set per table.

DBCC UPDATEUSAGE (0) WITH COUNT_ROWS, NO_INFOMSGS

SELECT OBJECT_NAME(ID) AS TableName, [Rows]

FROM dbo.SysIndexes

WHERE IndID IN (0,1)

AND OBJECTPROPERTY(ID,'ISTABLE') = 1

ORDER BY TableName

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

OptimalDevelopment Old Hand Points: 399 More actions · Answer 13

Jeff Moden (7/17/2009)
The following takes about the same time as the sp_MSForEachTable and has the distinct advantage of returning the row counts as a sortable single result set instead of a result set per table.
DBCC UPDATEUSAGE (0) WITH COUNT_ROWS, NO_INFOMSGS

SELECT OBJECT_NAME(ID) AS TableName, [Rows]
FROM dbo.SysIndexes
WHERE IndID IN (0,1)
AND OBJECTPROPERTY(ID,'ISTABLE') = 1
ORDER BY TableName

I like. I was able to sort, descending, by number of rows while weeding out tables I didnt wish to have in my report. Thanks! 😀

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

You bet. Thanks for the feedback, TJ.

But I wasn't the first one to suggest it on this thread. Go back and look at what dobrzak wrote earlier. Only thing missing was the DBCC UpdateUsage.

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