As a Developer / DBA, it’s a very frequent day to day routine task to delete unwanted objects – commonly Tables from SQL Server Instance. There may be different people have their own perception as per their system/environment to find unwanted objects, but here I am just discussing about empty table which belong to 0 (zero) records/rows and to list out those table here we can use any of below queries.
SELECT OBJECT_NAME(OBJECT_ID) AS TableName , SUM(row_count) AS [No. of Rows]
FROM sys.dm_db_partition_stats WHERE index_id in(0,1)
GROUP BY OBJECT_ID HAVING SUM(row_count)=0
ORDER BY TableName
———————————- OR ———————————————–
SELECT Obj.name as TableName, ps.row_count as [No. of Rows] FROM sys.indexes AS sIdx
INNER JOIN sys.objects AS Obj ON sIdx.OBJECT_ID = Obj.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps ON sIdx.OBJECT_ID = ps.OBJECT_ID
WHERE sIdx.index_id < 2 and ps.row_count=0 AND sIdx.index_id = ps.index_id
ORDER BY TableName
WikiDBA 