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.
Its nothing but just a simple dynamic SQL statement where "?" will be replaced by table name. So query will be like:
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.
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:
Let me know your opinions.
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