There are few ways to display row count of all the tables:
Let me explain both of the method:
1. Use Procedure: sp_msForEachTable:
This is the simplest way that we can make COUNT(*) to each table to find number of rows from each table. This is undocumented stored procedure,"sp_msForEachTable" gives ability to make a query to all tables.
EXEC sp_msForEachTable 'SELECT ''?'' AS TableName,COUNT(*) AS NoOfRows FROM ?'
Its nothing but just a simple dynamic SQL statement where "?" will be replaced by table name. So query will be like:
SELECT COUNT(*) FROM BuildVersion
This procedure, "sp_msForEachTable", will loop to each table for current database and count rows from each table. So it gives result like:
By this way, we can get row count from all the tables for current database. But question is: How to use this result set? How to find out which table has maximum rows? To find out this, I need to check each table's rows manually and then only I can find out which table has maximum rows. This is time consuming and not the good way to find out table with maximum rows. Let see another method on which we can find it out it easily.
2. Read information from system tables:
By reading system tables, we can also find out rows from each table. We can use following query to find out number of rows from each table.
SELECT st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS NoOfRows FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.tables st ON st.object_id = p.Object_ID INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name
NOTE: This method is used to get an *approximate* count for all tables.
This query solved my purpose. In this query, it will give result in single result set, so we can manipulate it easily.
E.g. List out all tables and sort it out based on the NumberOfRows.
So, my query will be:
SELECT st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS NoOfRows FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.tables st ON st.object_id = p.Object_ID INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name ORDER BY NoOfRows DESC
Let me know your opinions.
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015