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?

Answer 1

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

Answer 2

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 and i.rows=0

It will give table name and rows in that tables