Looking for tables with no data

Question

Post reply

Looking for tables with no data

itpcpro

Ten Centuries

Points: 1043
More actions
August 15, 2011 at 8:44 am

#241771

I want to run a script to find any tables with no data in them...
I have researched and found:
exec sp_MSforeachtable 'select ''?'' as TableName, count(*) as RecordCount from ?'
This will tell me record count for each table but I am not sure how to modify it to just find the tables with 0...
Thanks..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

select object_name(object_id) AS TableName, rows

from sys.partitions

where rows = 0 and index_id in (0,1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Something like this would do it: -

SELECT sub.tableName FROM

(SELECT tableName = sysob.name, numberOfRows = MAX(sysind.rows)

FROM sysobjects sysob, sysindexes sysind

WHERE sysob.xtype = 'U' AND sysind.id = OBJECT_ID(sysob.name)

GROUP BY sysob.name ) sub

WHERE sub.numberOfRows = 0

The above is untested as I'm away from my desk at the moment.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sysobjects and sysindexes are deprecated, should not be used for new development, will be removed in a future version of SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

itpcpro Ten Centuries Points: 1043 More actions · Answer 4

Thanks Gail... This looks like what I was looking for...