SQL SERVER – Simple Query to List Size of the Table with Row Counts

Sometimes we need simple query which can do the task, then complicated solution. Here is a simple query which list size of the table in MB with Row Counts. I often run at my customer understand how many different tables they have and what is the row counts as well as the size of the each table.

It is very powerful but simple query.

Solarwinds
USE [YourDBName] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO

Here is the result which we receive if we run above query on AdventureWorks sample database.

SQL SERVER - Simple Query to List Size of the Table with Row Counts rowcountwithsize

When you run above query, it usually runs in just a split seconds and it also does not lock any of your essential tables while it is running (only shared lock for select statement), hence this query is very much alright to run on your production database. Trust me, this may be simple but very powerful script you must keep in your bookmark.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – How to Find Row Count of Every Table in Database Efficiently?
Next Post
SQL SERVER – Observation – Adding a NULL Column and Table Space

Related Posts

13 Comments. Leave new

  • Here’s just rowcount but for all tables in all userdbs.

    –Run in SQLCMD Mode.
    –:Connect SQL1
    SET NOCOUNT ON;
    USE tempdb;
    CREATE TABLE #AllDBRowCounts(DB sysname,SchemaName sysname,TableName sysname,RowCounts bigint);

    INSERT INTO #AllDBRowCounts
    EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
    IF ”?” NOT IN (”master”,”model”,”msdb”,”tempdb”)
    BEGIN
    SELECT db_name() as DB,
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS RowCounts
    –,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    –CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
    –CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    GROUP BY t.Name, s.Name, p.Rows
    ORDER BY s.Name, t.Name
    END’;

    SELECT * FROM #AllDBRowCounts ORDER BY DB,SchemaName,TableName;
    DROP TABLE #AllDBRowCounts;

    GO

    Reply
  • Thank you!

    Reply
  • You might mention that counts could be off in certain circumstances… and that DBCC UPDATEUSAGE would be required to get accurate numbers. With caveats on running it often, especially on large databases, of course.

    Reply
  • Why would I get duplicates with this query? Is that because DBCC UPDATEUSAGE needs to be ran like Steve indicates? One of my tables is listed 3 times and each has different row counts.

    Reply
    • Hi,

      You should have some filtered index on your database.

      Since the script used JOIN with sys.indexes and then GROUP BY p.rows, you have different result. Every with different (and real) usage.

      Maybe you want to filter

      i.type = 1

      To only have info for CLUSTERED index.

      Reply
      • To respect the goal of the script.

        Could be better to change p.rows by MAX(p.rows) AS RowCounts,

        and remove p.Rows from the GROUP BY clause.

  • What does this total pages contain? How SQL server decides to allocate how many pages to a table?,

    Reply
    • One page is 8KB in size. If we insert more data and it is not able to fix it one page, it allocates next page and so one.

      Reply
  • Would you explain that the sum of used_pages divide by 128 […(SUM(a.used_pages) / 128.00…]?

    Reply
  • This was really great. I added a filter for just the table name I wanted. Before I was using

    exec sp_spaceused ‘[TableName]’

    But i like this better! Can anyone educate me plus and minus of each approach? I am a T-SQL white belt!

    Reply
  • i need with DB names as well.

    Reply
  • Praveen Kumar
    May 9, 2020 12:31 pm

    This is the correct and updated script.

    SET NOCOUNT ON;
    USE tempdb;
    IF OBJECT_ID(‘tempdb..#AllDBRowCounts’) IS NOT NULL
    DROP TABLE #AllDBRowCounts
    go
    CREATE TABLE #AllDBRowCounts(
    DB sysname,
    SchemaName sysname,
    TableName sysname,
    RowCounts bigint,
    Used_MB bigint,
    UnUsed_MB bigint,
    Total_MB bigint
    );

    INSERT INTO #AllDBRowCounts
    EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
    IF “?” NOT IN (“master”,”model”,”msdb”,”tempdb”)
    BEGIN
    SELECT db_name() as DB,
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS RowCounts
    ,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    GROUP BY t.Name, s.Name, p.Rows
    ORDER BY s.Name, t.Name
    END’;
    SELECT * FROM #AllDBRowCounts ORDER BY DB,Total_MB DESC
    DROP TABLE #AllDBRowCounts;
    GO

    Reply

Leave a Reply

Menu