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

Answer 2

To get the list of empty tables, we can use the below tsql –

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

And, to get a list of tables having at least one row of data, we can use the below tsql –

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Note: List of table included only 'User Table' i.e. Not included 'System Table'.

Answer 3

select a.rows as Rowcnt,
   b.name as Tbl_Name 
from sys.partitions a
join sys.tables b
   on a.object_id=b.object_id
where b.type='u' 
   and a.rows = 0

Answer 4

  SELECT name AS [TableList] FROM SYS.DM_DB_PARTITION_STATS s 
  INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
  WHERE row_count = 0