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

No comments:

Post a Comment

All opinions welcome! Please comment with any changes thoughts or discussion points.