TSQL to return (display) all tables row count in database - think about performance

In the forums and newsgroups this is a very common question that how to get all the tables rowcount or to get optimum values.

You may be aware using SELECT COUNT(*) statement, but be aware it will make full table scan to return the rowcount and think performance if you are running against a database-table that has millions of rows. Alternative way is to use sysindexes system table, in this case from the last updated statistics and to get updated values you have to use DBCC UPDATEUSAGE statement. Again this will have another overhead running that DBCC statement on a huge volume of tables.

To get immediate response and fine tune the performance for such requirement you can use the statement SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 instead of SELECT COUNT(*); you will definetly see an improvement in getting the speed of such queries in several times.

DisplayRowCount:

create procedure [dbo].[DisplayRowCount] as
declare @tabcnt int
declare @printline char (60)
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