I did some online research to better understand how to use sp_MSforeachtable. I found this article to be very helpful:

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

It contains this very simplistic example which clarified things for me:

use pubs

go

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

'insert into #rowcount select ''?'', count(*) from ?'

select top 5 * from #rowcount

order by tablename

drop table #rowcount

tablename rowcnt

----------------- -----------

[dbo].[authors] 23

[dbo].[discounts 3

[dbo].[employee] 43

[dbo].[jobs] 14

[dbo].[pub_info] 8