Table Row Counts for an Instance

• dbychen

  SSCommitted

  Points: 1889

  More actions

  November 16, 2006 at 11:41 pm

  #75095

  Hi There,

  Is there any quick way to get table row counts of each database within an instance? Any suggestions will be appreciated.

  Thanks in advance!

• mamidimahesh-127681

  SSC Eights!

  Points: 868

  More actions

  November 17, 2006 at 1:49 am

  #672522

  Try this script

  USE NORTHWIND

  SELECT sysindexes.rows, sysobjects.name

  FROM sysusers, sysobjects, sysindexes 

  WHERE sysusers.uid = sysobjects.uid and  sysindexes.id = sysobjects.id and  sysobjects.name not like '#%'

  and  OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1 and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0 

  ORDER BY INDEXPROPERTY(sysindexes.id, sysindexes.name, N'IsStatistics')

  It gives each table row count in a given database.

  --Mahesh

• Prakash Sawant

  SSCrazy

  Points: 2173

  More actions

  November 17, 2006 at 2:14 am

  #672526

  THis query should help you.

  Exec sp_MSforeachdb  'select name,rowcnt from ?..sysindexes where id in (select object_id(name) from ?..sysobjects where xtype =''u'') and status = 0'

   

  ------------
  Prakash Sawant

  http://psawant.blogspot.com