Find empty tables in SQL Server database

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


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


  • table - table name with schema name


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

Sample results

Empty tables in our SSRS repository:

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