How to find out which Table is not having rows in SQL Server?

 

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE DBName --Change this to the DB Name you want to script for.

GO

DECLARE @TableRowCount TABLE

( 

    TableName VARCHAR(255), 

    RowCnt INT 

) 

INSERT @TableRowCount 

  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 

SELECT 

    * 

FROM 

  @TableRowCount 

WHERE

    RowCnt = 0     

ORDER BY 

  RowCnt