How Many Records Are Stored In The Database?

Introduction

This blog provides you with a SQL query which will fetch the list of tables on the database with their number of records contained.

Getting Started

How many records are stored in the database? You might think that this is very difficult and want to write gigantic code to find out the number of records stored in a database.

You don’t believe fetching the number of records count from database is very easy and interesting. The following SQL Query fetches number of records contained by a table and calculates the size of data contained by each table.

Just copy the below SQL Query, put the code into SQL editor and run it. You will get the list of tables with their contained number of records like below image.

I know that there are various ways are available to fetch the number of records of a table like using COUNT function, one can get number of records of a table. But you must write below code for all your code and above query list outs all the table available in database in one go.

 SELECT  DISTINCT  
     CONVERT(NVARCHAR(50),o.Name) [Name],  
     --i.Name,  
     max(s.row_count) AS 'Rows',  
 CASE (8 * 1024* sum(s.reserved_page_count)) WHEN 0 THEN 0   
      ELSE CASE max(s.row_count) WHEN 0 THEN 0   
      ELSE (8 * 1024* sum(s.reserved_page_count)) / max(s.row_count) END END AS 'Bytes/Row',  
      CASE sum(s.reserved_page_count) WHEN 0 THEN 0   
     ELSE sum(s.reserved_page_count) * 8.0 / 1024 END AS 'Size in MB',  
      CASE sum(s.reserved_page_count) WHEN 0 THEN 0   
     ELSE sum(s.reserved_page_count) * 8.0 / (1024 * 1024) END AS 'Size in GB'      
 FROM  
     sys.dm_db_partition_stats s,  
     sys.indexes i,  
     sys.objects o  
 WHERE  
     s.object_id = i.object_id  
     and s.index_id = i.index_id  
     and s.index_id >0and i.object_id = o.object_id  
 GROUP BY o.Name  
 --HAVING SUM(s.row_count) > 0  
 order by [Size in GB] desc  

Result

 Name         Rows   Bytes/Row Size in MB Size in GB  
 -------------------- -------- --------- ---------- --------------------  
 sysschobjs      2557   384    0.937500  0.000915527343  
 sysobjvalues     649   1098   0.679687  0.000663757324  
 syscolpars      1128   348    0.375000  0.000366210937  
 sysprivs       2533   80    0.195312  0.000190734863  
 sysrscols      1339   152    0.195312  0.000190734863  
 syscerts       7    11702   0.078125  0.000076293945  
 sysidxstats     253   323    0.078125  0.000076293945  
 sysiscols      450   182    0.078125  0.000076293945  
 sysallocunits    204   281    0.054687  0.000053405761  
 sysasymkeys     0    0     0.046875  0.000045776367  

Let me explain how the SQL Query is working. The query uses three tables that are sys.dm_db_partition_stats, sys.indexes and sys.objects. The information of the tables is given below.

The query mainly uses two columns of the sys.dm_db_partition_stats table to calculates the size of data(in bytes, MB and Gigabite(GB)) each table contains that are reserved_page_count and row_count. The reserved_page_count returns the total number of pages reserved for storing and managing in-row data in this partition, regardless of whether the pages are in use or not. Always 0 for a columnstore index. The row_count returns the approximate number of rows in the partition.

sys.dm_db_partition_stats

Returns page and row-count information for every partition in the current database.

sys.indexes

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead.

sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

Thanks