How to get Table Names with Record Count in SQL Server Database

Consider our company is working on some audit process and they want to log the table names with record count in Audit table during different hours of the day OR we are planning for storage and want to know which table has the most records and plan the storage accordingly.

The below query will return us all the tables from SQL Server Database with record count. We will be using System tables sysindexes and sys.tables to get our record count with table names.

SELECT DISTINCT t.name AS TableName,
                i.rows AS RecordCnt 
FROM   sysindexes i
       INNER JOIN sys.tables t
               ON i.id = t.OBJECT_ID 
WHERE  t.is_ms_shipped = 0  
ORDER BY t.name

 

Fig 1: Get Record Count with Table Names from SQL Server Database

Video Demo: How to Get Record Count for all the tables on SQL Server Instance