Subscribe to this blog
Briefcase
Print
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
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