SQL Query to list number of records in each table in a database

Description:

In this example we explain that how to get total number of record in each table in SQL Server or SQL Query to fetch the total number of record in each table in SQL Server. Sometime you have requirements like list the number of records in each table in a database so different query for different user like sql query that provide the row counts and table names in a database. Or how to fetch the row count for all tables in a SQL Server database.

Keeping track of your row counts can be very useful for budgeting and capacity planning.

Suppose I have 100 table in my SQL Server database and I want to fetch all the table that have more than 500 record in each table so in this situation below is the sql query useful for us.

Below is the query to get exact row counts for all tables in sql server

Query:

            SELECT o.NAME,

              i.rowcnt

            FROM sysindexes AS i

              INNER JOIN sysobjects AS o ON i.id = o.id

            WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 and  i.rowcnt >500

            ORDER BY o.NAME