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

Question

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

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

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

Now... if you want to know a bit more about each table including the row counts, try this...

--_______________________________________________________________________________________________________________________

/**********************************************************************************************************************

Purpose:

Returns a single result set similar to sp_Space used for all user tables at once.

Notes:

1. May be used as a view, stored procedure, or table-valued funtion.

2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:

Rev 00 - 22 Jan 2007 - Jeff Moden

- Initital creation for SQL Server 2000

Rev 01 - 11 Mar 2007 - Jeff Moden

- Add automatic page size determination for future compliance

Rev 02 - 05 Jan 2008 - Jeff Moden

- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

**********************************************************************************************************************/

--===== Ensure that all row counts, etc is up do snuff

-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

-- execute the command below prior to retrieving from the view or UDF.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

SELECT DBName = DB_NAME(),

--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

TableName = so.Name,

TableID = so.ID,

MinRowSize = MIN(si.MinLen),

MaxRowSize = MAX(si.XMaxLen),

ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

RowModCtr = MIN(si.RowModCtr),

HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

FROM dbo.SysObjects so,

dbo.SysIndexes si,

(--Derived table finds page size in KB according to system type

SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

FROM Master.dbo.spt_Values

WHERE Number = 1 --Identifies the primary row for the given type

AND Type = 'E' --Identifies row for system type

) pkb

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1, --Table with clustered index

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND PERMISSIONS(so.ID) 0

GROUP BY so.Name,

so.UID,

so.ID,

pkb.PageKB

ORDER BY ReservedKB DESC

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

Jeff, that is awesome stuff! Thanks! I see what you're saying about the earlier post and the missing DBCC UpdateUsage... all around cool stuff!

Tony

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

Thanks for the feedback, TJ.

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

mikenason Old Hand Points: 320 More actions · Answer 4

Does anyone have a suggestion/example on how I can use SP_MSforeachtable to drop all tables in a database that have 0 rowcount...ie no data in the table

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please post new questions in a new thread. Thank you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jorge Serres Old Hand Points: 352 More actions · Answer 6

Jorge Serres

Old Hand

Points: 352

October 31, 2011 at 10:20 pm

#1401411

Simply use

exec sp_MSforeachTable 'sp_spaceused '

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

Jorge Serres (10/31/2011)
Simply use
exec sp_MSforeachTable 'sp_spaceused '

Oh... be careful, now. sp_spaceused in SQL Server 2000 doesn't always have accurate row counts. You have to use the "UPDATE USAGE" option and, if you've never done it before, can take a bit o' time to execute.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Jeff Moden (7/17/2009)
Now... if you want to know a bit more about each table including the row counts, try this...
--_______________________________________________________________________________________________________________________
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.

Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC

Nice script Jeff.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kelley Fitz SSC Enthusiast Points: 134 More actions · Answer 9

as ? is your tablename you can use

sp_MSforeachtable 'SELECT COUNT(*) AS [?] FROM ?'

Kelley Fitz SSC Enthusiast Points: 134 More actions · Answer 10

The ? is the table name

EXEC MSFOREACHTABLE 'SELECT COUNT(*) AS ''?'' FROM ?'

TABLENAME

COUNT

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

Kelley Fitz (5/4/2015)
The ? is the table name
EXEC MSFOREACHTABLE 'SELECT COUNT(*) AS ''?'' FROM ?'
TABLENAME
COUNT

Do you realize that you posted almost the same answer one year ago on this 10 year old thread?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Luis Cazares (5/4/2015)
Kelley Fitz (5/4/2015)
The ? is the table name
EXEC MSFOREACHTABLE 'SELECT COUNT(*) AS ''?'' FROM ?'
TABLENAME
COUNT
Do you realize that you posted almost the same answer one year ago on this 10 year old thread?

Annual reminder for the thread. :-D:-D:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 13

SQLRNNR (5/4/2015)
Luis Cazares (5/4/2015)
Kelley Fitz (5/4/2015)
The ? is the table name
EXEC MSFOREACHTABLE 'SELECT COUNT(*) AS ''?'' FROM ?'
TABLENAME
COUNT
Do you realize that you posted almost the same answer one year ago on this 10 year old thread?
Annual reminder for the thread. :-D:-D:-D

I think the 2nd answer is better than the 1st. 😎

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ksiozios SSC Enthusiast Points: 149 More actions · Answer 14

This may have been answered already but this worked for me:

sp_MSforeachtable "select '?', count(1) from ?"

ronaldriveros Valued Member Points: 67 More actions · Answer 15

create table #t (i int)
EXEC sp_MSForEachTable 'insert into #T select count(*) from ?'
select sum(i) from #t