Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow

Support > Technical Documents > Document Types > Tips > Tip: Getting Row Counts in User Tables

Tip: Getting Row Counts in User Tables

How to get row counts for user tables in all versions of Sybase SQL Server and Adaptive Server Enterprise. Includes information about the optdiag utility introduced in 11.9.x.
RSS Feed


Adaptive Server Enterprise keeps row counts of tables in the first OAM page for the object. The system table sybstabstats, introduced in Adaptive Server 11.9.1, also keeps the row count for all user tables. Depending on which server version you are using, there are several ways to obtain the row count for any table:

  • Querying the table (all versions).

  • Using sp_spaceused (SQL Server 4.8 and higher).

  • Using the optdiag utility (11.9.2 and higher).

Querying the table (all versions)

In all versions of Adaptive Server and Sybase SQL Server, you can get row counts for any table by querying the table directly. The syntax is:

select count(*) from table_name

Using sp_spaceused (SQL Server 4.8 and higher)

To get a row count indirectly, without querying the table, use the sp_spaceused system stored procedure. Keep in mind that sp_spaceused gives an estimate based on an average number of rows per page, and it is more accurate if you run update statistics or dbcc checktable first. The syntax is:

sp_spaceused table_name

The row count is returned in the column rowtotal.

Using the optdiag utility (11.9.2 and higher)

The optdiag utility, introduced in Adaptive Server 11.9.2, displays statistics that includes Data row count. The syntax is:

optdiag statistics database_name..table_name -Uusername -Ppassword

For more information on optdiag, see the "Statistics Enhancements" chapter in New Functionality in Adaptive Server Enterprise 11.9.2.


Last Revised: Jun 21, 1999
Product: SQL Server Monitor, Adaptive Server Enterprise
Technical Topics: Database Admin
Business or Technical: Technical
Content Id: 20391
Infotype: Tips

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics