find empty tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

find empty tables

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2007-06-28 : 13:49:45

I have a database with 120 tables many of which don't have any data in them. How can I find all tables that are empty?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 13:52:25

you could run sp_spaceused on each of them. or look in sysindexes for those that have zero rowcount.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 14:30:34

I guess the 2005 way is not to use sysindexes, as it's deprecated. should rather use sys.dm_db_partition_stats:


select o.name, sum(s.row_count) 
from sys.objects o 
join sys.dm_db_partition_stats s 
on o.object_id=s.object_id
where o.type='U'
group by o.name
order by sum(s.row_count) desc

elsasoft.org

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2007-06-28 : 14:53:06

Thank you - the 2005 version works great! I appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources