Find tables without data

Question

How can we retrieve all tables in database without data (as in, there are no rows in table) in the case of a Microsoft SQL Server? Is there any method?

Accepted Answer

Try this

```
   SELECT   TableName=OBJECT_NAME(OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats
   WHERE    index_id in (0 ,1)
   GROUP BY OBJECT_ID
   HAVING   SUM(row_count)  = 0
```

OR If u need only user defined tables then use this

```
   SELECT TableName=OBJECT_NAME(s.OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats s
   JOIN     sys.tables  T
   ON       T.object_id = S.object_id       
   WHERE    index_id in (0 ,1)
   and      T.type  = 'U'
   GROUP BY s.OBJECT_ID
   HAVING   SUM(row_count)  = 0
```

Find tables without data

Solution

Problem

Related problems