August 15, 2011 at 8:44 am
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..
August 15, 2011 at 8:54 am
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
August 15, 2011 at 8:56 am
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.
August 15, 2011 at 9:03 am
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
August 15, 2011 at 9:05 am
Thanks Gail... This looks like what I was looking for...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy