Could not find stored procedure 'sp_msforeachtable' while looping through server for stats (SSMS)

I've written this to loop through each database on a server, collecting the statistics for each table and storing them in a temp table. Eventually, I'll integrate this into a more permanent structure, but for now I'm just trying to get this working. My problem is, after 57 databases, I get the error stating it can't find the stored procedure sp_msforeachtable.

I've verified that this stored procedure exists on every database on the server and on the server level.

I've excluded this database in the findings by adding it to the "where name not in" condition, and it just moves to the next one in the list and gives the same error.(I've confirmed it exists on the next database also). I've actually done this for the next 6 databases.

This is causing me to not collect accurate information. Am I running out of resources somewhere?

 DECLARE @Database TABLE (DbName SYSNAME); IF OBJECT_ID('tempdb.dbo.#TableLvlSizes', 'U') IS NOT NULL BEGIN PRINT 'dropping table' DROP TABLE tempdb.dbo.#TableLvlSizes; END CREATE TABLE #TableLvlSizes ( TableName nvarchar(128) ,NumberOfRows varchar(50) ,ReservedSpace varchar(50) ,TableDataSpace varchar(50) ,IndexSize varchar(50) ,unused varchar(50)) DECLARE @DbName AS SYSNAME; DECLARE @Sql1 AS VARCHAR(MAX); SET @DbName = ''; INSERT INTO @Database (DbName) SELECT NAME FROM sys.databases where name not in ('tempdb') ORDER BY NAME ASC; WHILE @DbName IS NOT NULL BEGIN SET @DbName = ( SELECT MIN(DbName) FROM @Database WHERE DbName > @DbName ); print @DbName; SET @Sql1 = 'USE ' + @DbName + '; ' + ' Exec sp_msforeachtable ''insert into #TableLvlSizes exec sp_spaceused [?]'' ' Exec (@SQL1); END 

2 Answers

If someone is using Azure SQL, they will not find sp_MSforeachtable since it is not available in Azure SQL.

You may need to create one for yourself.

Since you already verified that the stored procedure does in fact exist, I believe your database is case sensitive. Therefore, the error is still accurate. Basically, the stored procedure with the case you used does not exist. The actual procedure name is sp_MSforeachtable

In your code, you are using the following: Exec sp_msforeachtable

If you change your code to use the proper case for the stored procedure to be sp_MSforeachtable, it should work:

SET @Sql1 = 'USE ' + @DbName + '; ' + ' Exec sp_MSforeachtable ''insert into #TableLvlSizes exec sp_spaceused [?]'''