This isn’t necessary a critical part of a DBA’s job but, at times, it can be useful to have an idea of how many rows are in your databases.
The simplest way to get it is with this query:
select sum(rowcnt) from sysobjects, sysindexes where sysindexes.id = sysobjects.id and sysindexes.indid in (0, 1) and sysobjects.xtype = 'u'
This will get you the sum of rows in the entire database, for users objects only. If you want the table-by-table breakdown, you can simply add the name of the object in the query:
select sysobjects.name, sysindexes.rowcnt from sysobjects, sysindexes where sysindexes.id = sysobjects.id and sysindexes.indid in (0, 1) and sysobjects.xtype = 'u' order by sysobjects.name
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.