August 23, 2010

SQL SERVER: Display Row count of all the tables

Recently we have performance issue in SQL SERVER and we decided to move some large tables to different file group, so we can improve SQL SERVER performance, as it uses other disks I/O. Now challenge is to identify large tables from many tables (approx 700 tables).

There are few ways to display row count of all the tables:
  1. Use Procedure: sp_msForEachTable (Simplest way)

  2. Read information from system tables ( approximate count, Preffered)

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.

5 comments:

  1. Great information.
    Thanks a lot!!

    ReplyDelete
  2. I liked your first method because is gives you actual table row count. I modified the code from above and added a temp table to allow sorting and one result set. Have fun.

    CREATE TABLE #tmpBus
    (
    TableName VarCHAR(50),
    NumofRows INT
    )

    INSERT INTO #tmpBus
    --Exec SpGetRecords 'Params'
    EXEC sp_msForEachTable 'SELECT ''?'' AS TableName,COUNT(*) AS NoOfRows FROM ?'

    select * from #tmpBus order by NumofRows desc
    drop table #tmpBus

    ReplyDelete
  3. Hi,
    The method that you have used is also correct.

    The main problem in the query that you have build is, when you run it against heavy database, having multiple tables, with million rows, it will take much time.

    The query, that I have used is reading information from Allocation units, so there is no actual scanning of tables. That's why, I mentioned that, this query gives APPROXIMATE result, but its much faster.

    I appreciated your query which gives me exact count of table, but its much slower when database is large

    Tejas
    SQLYoga.com

    ReplyDelete
  4. Hi..

    find out row count in any of the tables in a given database with where condition to each table how to do that in a single stored procdure.

    ReplyDelete
  5. Hi Sharath,

    I would prefer to use CTE (Common Table Expression) to get result in CTE and then query CTE with Tablename that I wanted to know the count of the rows.

    Please find query as follows:

    ;with CTE as(
    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
    )
    SELECT *
    FROM cte
    where TableName LIKE 'TableName'

    Thanks,
    Tejas
    SQLYoga

    ReplyDelete