tsql - Select all empty tables in SQL Server

Question

How to get the list of the tables in my sql-server database that do not have any records in them?

Answer 1

On SQL Server 2005 and up, you can use something like this:

;WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      index_id = 0 OR index_id = 1
   GROUP BY 
      OBJECT_ID
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0

The inner select in the CTE (Common Table Expression) calculates the number of rows for each table and groups them by table (OBJECT_ID), and the outer SELECT from the CTE then grabs only those rows (tables) which have a total number of rows equal to zero.

UPDATE: if you want to check for non-Microsoft / system tables, you need to extend the query like this (joining the sys.tables catalog view):

;WITH TableRows AS
(
   SELECT 
       SUM(ps.row_count) AS [RowCount], 
       t.Name AS TableName
   FROM 
       sys.dm_db_partition_stats ps
   INNER JOIN
       sys.tables t ON t.object_id = ps.object_id
   WHERE 
       (ps.index_id = 0 OR ps.index_id = 1)
       AND t.is_ms_shipped = 0
   GROUP BY 
       t.Name
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0