How to find empty tables in SQL Server : Quick Tip

These quick tips are short and sweet. They are nothing more than an explanation and some code. If you have questions or want to see something more, feel free to drop a line.

This came as a need for a project I was working on. We wanted to find all the tables that were empty and didn't have a primary key. We copied a database to test and truncated everything, we wanted to know what was being repopulated. This may not be the most beautiful way of doing this, but it works.

select distinct obj.name as TableName, obj.object_id,

part.rows as RowCounts from sys.tables tbl

inner join sys.partitions as part on tbl.object_id = part.object_id

inner join sys.objects as obj on  tbl.name = obj.name

inner join sys.indexes as idx on obj.object_id  = idx.object_id

where rows = 0

and is_primary_key = 0

and idx.type <= 1