Getting Count of Result Sets of SP (SP_MSForeachtable)

I was going to list all of the Tables in my DB which have no records.
As same as most of the times I used SP_MSforeachtable like:

EXEC sys.sp_MSforeachtable 'if (select Count(*) from ?) = 0 
                               select ''?'' ,Count(*) from ? '

​x
 
EXEC sys.sp_MSforeachtable 'if (select Count(*) from ?) = 0                                select ''?'' ,Count(*) from ? '​

And this gives me many result sets which I need to count them now.

I know there are other ways to work with tables but I was thinking getting count of sys.sp_MSforeachtable (or any SP) result sets
might be usefull.

Also if there is way to union all this result sets I will appreciate to share.

Answer

You can use this query to extract the information you require

SELECT T.name, ST.row_count
FROM SYS.tables T
INNER JOIN SYS.dm_db_partition_stats ST ON T.object_id = ST.object_id
WHERE ST.row_count = 0

 
SELECT T.name, ST.row_countFROM SYS.tables TINNER JOIN SYS.dm_db_partition_stats ST ON T.object_id = ST.object_idWHERE ST.row_count = 0​

Advertisement

Answer