Find empty tables in Azure SQL Database

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.