SPROC to get row count from all tables

[sfriedman451]

I am trying to create a SPROC to get row counts from all the tables in a database. To experiment I've created one that works (or I should say doesn't work) with the PUBS database.

Use Pubs
GO
Create Table #Tblnames (tblename varchar(60))
GO

Select name into #Tblenames from sysobjects where xtype = 'U'
Select name from #Tblenames
Declare C1 cursor FOR Select name from #Tblenames FOR READ ONLY

Open C1
Declare @tbl varchar(65)
Fetch c1 into @tbl
Select @tbl
Select count (*) from @tbl
Next c1
DeAllocate c1

It seems to have a problem withthe Select count (*) from @tlb - it tells me @tbl is not defined. Can I use a variable here?

Is there a better way to do this type of operation?

Thanks in advance

Steve

 

[tlbroadbent]

Try this. It is faster and will be accurate when index statistics are up to date.

Select
TableName=left('['+u.name+'].['+o.name+']',60),
RowCnt=Max(i.rowcnt)
From sysindexes i
Join sysobjects o
On i.id=o.id
Join sysusers u
On o.uid=u.uid
Where o.type='u'
And left(o.name,3)<>'dtp'
Group By u.name, o.name

Exec sp_updatestats in the database to update statistics. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

Hi Terry:
>>It is faster and will be accurate
>>when index statistics are up to date.

I was wondering if you think it matters just how accurate sfriedman needs the numbers to be. i.e. It is my understanding that statistics, even when auto-updating, are not updated after every Insert/Update/Delete on a table. Rather, I think the frequency at which the statistical information is updated is determined by the volume of data in the column or index and the amount of changing data.

Therefore, statistics are never really 'perfectly' up-to-date, but only up-to-date as the last auto-refresh. Which can be good enough for many purposes. But if sfriedman needed to know just exactly how many rows were in a table, he would have to do a Count().

Agree? Am I missing something?

bp

 

[sfriedman]

bperry is correct, I need to know accurately how many rows each table has since were looking at this after bulk inserts.

 

[tlbroadbent]

Brian,

Thanks for the input. You are correct that count(*) will always return the correct count. My experience is that the rowcnt in sysindexes is usually correct but under some circumstances it can be wrong.

Steve,

The following script will count rows in all tables of a database. It uses the undocumented stored procedure, sp_msforeachtable.

--Script to count rows in all tables in a database.
--Inhibit rows affected messages
Set nocount on

--Create temp table to hold counts
Create table #tbl(ID int, tblname varchar(60), rowcnt int)

--Create SQL statement to count rows
Declare @sql varchar(100)
Select @sql='Select ID=object_id(''?''),tblname=''?'',rowcnt=count(*) From ?'

--Execute SQL statement with sp_MSForEachTable
--and insert counts into temp table
Insert #tbl
Exec sp_msforeachtable @sql

--List the counts by table
Select * From #tbl Order By tblname

If you are interested in comparing the output of counting the rows to the statistics in sysindexes, add the following script to the end of the count script.

--Optional update stats
--exec sp_updatestats

--List table name, sysindexes rowcnt and actual
--count of rows in each table

Select
tblname=left('['+u.name+'].['+o.name+']',60),
Match=Case When i.rowcnt=t.rowcnt Then 'True' Else 'False' End,
sicnt=i.rowcnt, actcnt=t.rowcnt
From
(Select ID, rowcnt=max(rowcnt)
From sysindexes Group By id) i
Join sysobjects o
on i.id=o.id
Join sysusers u
on o.uid=u.uid
Join #tbl t
On o.id=t.id
where o.type='u'
and o.name<>'dtproperties'
Order By tblname
go

--cleanup
set nocount on
drop table #tbl
go Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

That &quot;sp_msforeachtable&quot; is quite interesting. And that's a nice script.

I once was working on an railroad car-tracking application. There were times when it was critical to know just exactly how many cars were on a train, so that (a) users would know whether the train would completely fit onto a siding without sticking out on the highway, and (b) so other trains would not come up and ram into them.

bp

 

[sfriedman451]

Terry's solution works like a charm. Is there a way I can pass what database to use as a parameter in the execute statement. It tells me that I cannot use &quot;USE&quot; in a trigger or SPROC, so to use this I either have give the USE db statement first, or find a way to pass that parameter.

 

[tlbroadbent]

If you create your stored procedure in the master database and prefix the SP name with sp_ (sp_yourprocname) the procedure will be considered a system stored procedure. System SPs are interesting because they can be executed from any database. Another feature is that the database context of the execution can be temporarily changed by qualifying the SP name with the name of the database where you desire to execute the SP.

Example: Execute SP in the UtilityDB

Exec UtilityDb.dbo.sp_CountRowsInAllTables

SQL server handles the database context change transparently. When the the SP execution terminates, context is returned to the original database. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.