[SOLVED] Could not find stored procedure 'sp

Could not find stored procedure 'sp_msforeachtable'

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

Solution

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 [?]'''