Find Number of Rows in User Tables 3

[CrystalVis]

I have about over 600 user tables in the database. I want to find the total number of rows in each user table for all the user tables. Is there a simple way acomplish this task other than select * from each user table for all 600 user tables? Your help/sugesstion is greatly appreciated.
TIA

 

[JohnDTampaBay]

sp_MSForEachTable 'SELECT COUNT(*) AS ''?'' FROM ?'

This undocumented procedure should do the trick.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[CrystalVis]

Angel,
Thanks for your quick respond. Is it a pre-created stored proc in the db? I don't understand this statement:
'SELECT COUNT(*) AS ''?'' FROM ?'

if you don't mind, can you please elaborate.
Thanks

 

[JohnDTampaBay]

sp_MSForEachTable is an undocumented procedure that resides in the master database. The procedure ...

1 - accepts a statement passed as a parameter
(for example, SELECT COUNT(*) AS '?' FROM ?)

2 - loops through a list of tables in the current
database and, for each table, ...

a - substitutes the name of the table with
the ? in the parameter statement

b - executes the statement

There is another procedure named sp_MSForEachDB that acts similarly for databases.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[CrystalVis]

Angel,
Thank you for the explanation. I was able to retrieve the result in Query Analyzer. Is it possible to dump the output into a temp table to do report on? Again, thanks for your help.

 

[JohnDTampaBay]

Thanks for the star. How does this work for you?

CREATE TABLE ##Cnts (
   Table_name varchar(100) NOT NULL
   , Cnt int NOT NULL
   )

sp_MSForEachTable 'INSERT ##Cnts SELECT ''?'' AS Table_Name, COUNT(*) Cnt FROM ?'

SELECT * FROM ##Cnts

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[CrystalVis]

Angel,
I'll give it a try and let you know. I tried SELECT INTO hoping that it will create the #table dynamic but receive an error. I will try your suggestion and post the result. Many thanks for all your helps.

 

[jaywalk]

I have a ridiculously large database at work, with a multitude of tables, and had the same problem. However, using a select count(*) from tablename wasn't terribly efficient, so I ran sp_spaceused instead, which is fine in most cases:

CREATE TABLE #spaceused
(name varchar(20), rows int, reserved varchar(20), 
 data varchar(20), index_size varchar(20), unused varchar(20))

DECLARE @TableName varchar(200)

SET NOCOUNT ON

DECLARE space_cursor CURSOR FOR 
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  
OPEN space_cursor
  
FETCH NEXT FROM space_cursor 
INTO @TableName
  
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT #spaceused
	EXEC sp_spaceused @TableName

-- Get the next author.
	FETCH NEXT FROM space_cursor 
	INTO @TableName
END
  
CLOSE space_cursor
DEALLOCATE space_cursor

SET NOCOUNT OFF

PRINT 'Total Space Used'
select 	sum(convert(int, LEFT(reserved, LEN(reserved) - 3))) 'Reserved', 
	sum(convert(int, LEFT(data, LEN(data) - 3))) 'Data'
from #spaceused

PRINT 'Odd shaped tables'
SELECT convert(int, LEFT(reserved, LEN(reserved) - 3)) AS 'Size', * from #spaceused
WHERE convert(int, LEFT(data, LEN(data) - 3)) > (rows * 8.096)
AND rows > 0
ORDER BY Size DESC

PRINT 'All tables'
SELECT convert(int, LEFT(reserved, LEN(reserved) - 3)) AS 'Size', * from #spaceused
ORDER BY Size DESC

DROP TABLE #spaceused

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

Jaywalk

 

[CrystalVis]

Angel,
Your last post was exactly what I need. You are a life saver...

Jaywalk,
I'll try your method. Thanks for your helps...

 

[Glowworm27]

Jaywalk,

I just gave you a Star, very nice code, with one little exception, some of my table names are larger than 20 characters, so the SQL would blow up. I changed the Varchar(20) fields to Varchar(50) and the SQL worked wonderfully!

Good Job.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[hmckillop]

Jaywalk

another way of doing relatively the same thing, is to query the sysindexes (indid 0,1) for each table and it will give you a good rowcount. Only thing to watch is that sometimes after backups have been restored the index count might be a few out.
This will get you all user tables in one go and return the total rows

select
 syo.name as TableName,
 syi.rowcnt as TotalRows
From
	Sysobjects syo
inner join sysindexes syi on syo.id = syi.id and syi.indid in (0,1)
Where syo.Xtype = 'U' --user tables only

"I'm living so far beyond my income that we may almost be said to be living apart