Select count(*) from all tables

[Guest_imported]

Hi,

I need to be able to do a select count(*) from all the tables in a database (SQL Server D/B). Usually I would do
select count(*) from (select table_name from all_tables);
but I'm not sure how to do it in SQL Server.
I'd be much obliged for any help.

Thanks.

 

[fluteplr]

This is probably not exactly what you want, but you should be able to modify it. The key is the sp_msforeachtable stored proc.

set quoted_identifier off

sp_msforeachtable
"declare @count int select @count=count(*)from ? if @count > 0 begin print '?' print @count end"

 

[tlbroadbent]

Here is another method that is very fast. It uses the sysindexes table so you need to make sure statistics are up-to-date.

Select o.name, i.rows
from sysindexes i
inner join sysobjects o
on i.id=o.id
where i.indid =0
and o.type='u' Terry L. Broadbent
Programming and Computing Resources

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

 

[Guest_imported]

Thanks very much for your help, I'll try these methods out.
Thanks again.