Number of rows of all the tables in the database

Is there a way to get a list of all the table and the
number of rows it contains?
Thanks
Anna> Is there a way to get a list of all the table and the
> number of rows it contains?
http://www.aspfaq.com/2428
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Anna,
> Is there a way to get a list of all the table and the
> number of rows it contains?
You can also open a cursor and get the counts of each table in a
loop. This procedure allows you to do a bit of filtering of the
tables.
use Northwind
go
create procedure get_rowcounts (
@.table sysname = '%'
) as
declare @.sql varchar(1000)
create table #Tmp (table_name sysname, row_count int)
declare csrTable cursor local fast_forward for
select table_name from information_schema.tables
where table_name like @.table
and table_type = 'BASE TABLE'
order by table_name
open csrTable
fetch next from csrTable into @.table
while (@.@.fetch_status = 0) begin
set @.Sql = 'select ''' + @.table +
''', count(*) from [' + @.table + ']'
-- print @.sql
insert #tmp exec (@.sql)
fetch next from csrTable into @.table
end
close csrTable
deallocate csrTable
select row_count, table_name from #tmp
go
exec get_rowcounts 'Customers'
exec get_rowcounts 'Order%'
exec get_rowcounts
go
drop procedure get_rowcounts
-- Linda|||Thanks
>--Original Message--
>Anna,
>> Is there a way to get a list of all the table and the
>> number of rows it contains?
>You can also open a cursor and get the counts of each
table in a
>loop. This procedure allows you to do a bit of filtering
of the
>tables.
>
>use Northwind
>go
>create procedure get_rowcounts (
> @.table sysname = '%'
>) as
>declare @.sql varchar(1000)
>create table #Tmp (table_name sysname, row_count int)
>declare csrTable cursor local fast_forward for
>select table_name from information_schema.tables
>where table_name like @.table
>and table_type = 'BASE TABLE'
>order by table_name
>open csrTable
>fetch next from csrTable into @.table
>while (@.@.fetch_status = 0) begin
> set @.Sql = 'select ''' + @.table +
> ''', count(*) from [' + @.table + ']'
>-- print @.sql
> insert #tmp exec (@.sql)
> fetch next from csrTable into @.table
>end
>close csrTable
>deallocate csrTable
>select row_count, table_name from #tmp
>go
>exec get_rowcounts 'Customers'
>exec get_rowcounts 'Order%'
>exec get_rowcounts
>go
>drop procedure get_rowcounts
>
>-- Linda
>
>.
>|||Hi, you can try to use select count(*) from tablename.
Yanling
>--Original Message--
>Is there a way to get a list of all the table and the
>number of rows it contains?
>Thanks
>Anna
>.
>sql