Blog Post

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

Rate

Share

Share

Rate