April 6, 2006 at 8:20 am
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. */
April 6, 2006 at 8:28 am
Hello Bill,
Can you try this one
sp_MSforeachtable @command1="select count(*) from ?"
Thanks and have a great day!!!
Lucky
April 6, 2006 at 9:24 am
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 ?"
April 6, 2006 at 9:58 am
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)
April 6, 2006 at 10:36 am
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
April 7, 2006 at 7:58 am
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
October 12, 2007 at 8:19 am
You can use this as well:
sp_MSforeachtable @command1 ="print 'TABLE NAME: ' + '?' SELECT COUNT(*) FROM ?"
October 12, 2007 at 8:11 pm
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
Change is inevitable... Change for the better is not.
November 16, 2007 at 9:25 am
/* 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;'
July 16, 2009 at 2:01 pm
Try this:
[font="Courier New"]
USE *your_db_name_here*;
GO
sp_MSforeachtable 'select ''?'' Tablename, count(*) ''Rows'' from ?';
[/font]
July 17, 2009 at 8:06 am
... 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."
July 17, 2009 at 9:12 am
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. 🙂
July 17, 2009 at 9:24 am
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
Change is inevitable... Change for the better is not.
July 17, 2009 at 4:51 pm
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! 😀
July 17, 2009 at 9:31 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply