About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Saturday 1 October 2011

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