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?


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.


USE DBName --Change this to the DB Name you want to script for.
    TableName VARCHAR(255), 
    RowCnt INT 
INSERT @TableRowCount 
  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 
    RowCnt = 0     


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.