Table Row Counts 1

[quietstormtw]

Hi all,

I am looking to perform a comparison row count. I will take a count of all records in 20 different databases today. Next week, I would to perform the same fucntion.

My adminstrators inform me that our all_table & all_indexes tables are only analyazed once a week, which will not provided an accurate count when I need to run my SQL.

Since I have to utlize the Select Count(*) From 20 Tables...I am in need all some serious aid in getting the table names and num_rows added into the same query.

Can this be done w/o having to create 20 different queries?

Thanks in advance!!

 

[mutley1]

Hi quietstorm,

If you run this it should run through all user tables and return a rowcount for the database. You can amend it to insert into a table and add a date stamp if you want to append next week and compare. Another kind member of this community helped me with this a few weeks ago - I think it was SQLDenis.

select @tabcnt = count (*) from sysobjects where type = 'U'
 
If @tabcnt != 0
BEGIN
  select 'TABLE NAME'= convert (varchar (50), o.name), ROWS=i.rows
    from sysobjects o, sysindexes i
    where o.type = 'U'
      and o.id = i.id
      and i.indid in (0,1)
  order by o.name
END
 
select @printline = '(' + convert (varchar(10), @tabcnt) +
                       ' tables in ' + DB_NAME() + ')'
 
print ''
print @printline
GO

Cheers,

M.

 

[gmmastros]

You can use the undocumented system stored procedure to do this.

Create 
Table  #Temp
       (TableName VarChar(200), 
       RecordCount Integer, 
       TheDate DateTime)

exec sp_msforeachtable 'Insert Into #Temp Select ''?'' As TableName, Count(*), GetDate() From ?'

Select * from #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[quietstormtw]

Thank you for both of your suggestions...but I believe that I had posted this issue in the wrong.

Albeit - I figured out something that works. It's not as clean as yours - but it works just fine...for now...

Select
t.table_name,
(Select count(*) From table1)
From
all_tables t
Where
t.table_name = 'TABLE1'
UNION
Select
t.table_name,
(Select count(*) table2)
From
all_tables t
Where
t.table_name = 'TABLE2'