List tables by the number of rows in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    This query returns a list of tables in a database with their number of rows.

    Query

    select schema_name(tab.schema_id) + '.' + tab.name as [table],
           sum(part.rows) as [rows]
       from sys.tables as tab
            inner join sys.partitions as part
                on tab.object_id = part.object_id
    where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
    group by schema_name(tab.schema_id) + '.' + tab.name
    order by sum(part.rows) desc
    

    Columns

    • table - table name with schema name
    • rows - number of rows in a table

    Rows

    • One row: represents one table
    • Scope of rows: all tables in a database including tables without rows
    • Ordered by: number of rows in descending order, from largest to smallest (in terms of number of rows)

    Sample results

    Tables by number of rows in the AdventureWorksLT database:

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept