How many rows per table? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

How many rows per table?

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-25 : 17:04:44

Here's a little script to tell you how many rows you have per table in a 2005 database.

Nothing fancy, but I found it useful.


declare @t table (partition_number int not null, name sysname not null, rows bigint not null)

insert @t
select 
    i.partition_number
    ,o.name
    ,i.rows
from sys.partitions i
join sys.objects o on i.object_id=o.object_id
where o.type='U' and index_id IN (0, 1)

select * from 
(
    select null as partition_number, 'TOTAL' as name, sum(rows) as rows from @t
    union all 
    select * from @t
) a 
order by 1, 3 desc

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-25 : 19:15:27

incidentally, total rows in one of the dbs i am working on at the moment: 6424622745

big!

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 01:38:49

Note that You need to run

DBCC UPDATEUSAGE ('database_name') WITH COUNT_ROWS

for accurate row count

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-02-26 : 09:47:41

You know you are counting the rows in all the user tables' indexes? (though not any rows in indexes on views)

If you only want to count heaps and clustered index sizes, you'd need to filter on index_id IN (0, 1)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-26 : 12:27:31

thanks, fixed. now my count is only 6408980460

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-27 : 01:42:03

Some other approaches I suggested
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources