Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 
 
    

Comments

Posted by Jason Brimhall on 2 October 2011

Here's another method

select DB_NAME(database_id) as DBName,OBJECT_NAME(object_id) as ObjName,record_count,index_type_desc as RecordCountFromIdxType

from sys.dm_db_index_physical_stats(db_id(),null,null,null,'detailed')

Where index_id in (0,1)

And index_level = 0

Posted by vinay pugalia on 2 October 2011

Thanks Jason !

Posted by Anonymous on 5 October 2011

Pingback from  Link Resource # 27 : Oct 03 – Oct 06 «  Dactylonomy of Web Resource

Posted by Boss Adam on 9 November 2011

Hi Vinay,

Sorry to say but "EXEC sp_msForEachTable" does not work on my SQL 2K8 R2.

Here's the error::

Msg 2812, Level 16, State 62, Line 3

Could not find stored procedure 'sp_msForEachTable'.

Any other suggestions ??

Posted by vinay pugalia on 10 November 2011

Hello Adam,

Could you please post the result of SELECT @@Version

Leave a Comment

Please register or log in to leave a comment.