In this programming tutorial we will learn how to count rows of all tables in a sql server database. we can find out all the tables present in a database along with their row count with the help of system tables.

Count rows of all tables in a sql server database

Select tbl.name, si.rows from sysindexes si
inner join sys.tables tbl on si.id = tbl.object_id and indid < 2

Sys.tables has each and every information about all the tables in a given database. While sysindexes contains all the information of all the indexes present in the database.


si.rows of the sysindexes give the row count associated with the given index.

Tbl.name gives the table name and comes from the sys.table.

si.id and tbl.object_id gives the unique ID associated with each table present in the database.

As far as indid is concerned, it stands for the ID of the index, 1 is for clustered index and (> 1) is for nonclustered index.

For SQL 2000 we need to do little modification in order to get the desired results.

select tbl.name, dd.rows from sysindexes dd

inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U'

So that's it. This is the way to count rows of all tables in a ms sql server database.

I love your feedback.