Find empty tables in Azure SQL Database

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

Table of Contents:


    This query returns a list of tables without rows in a database.

    Query

    select schema_name(tab.schema_id) + '.' + tab.name as [table]
    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
    having sum(part.rows) = 0
    order by [table]
    

    Columns

    • table - table name with schema name

    Rows

    • One row: represents one table
    • Scope of rows: only empty tables (without rows)
    • Ordered by: schema and table name

    Sample results

    Empty tables 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