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

Question

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

Are you ignoring system databases (other than tempdb I see you are ignoring)? May be a system db doesnt have that. Do you know what DB is missing it? — Brad, Mar 05 '21 at 20:58
and how does this work? SELECT MIN(DbName) FROM Database WHERE DbName > DbName You should be using databaseID not name. Not sure how you can have a string greater than others. (had to remove ampersand from above code to work) — Brad, Mar 05 '21 at 20:59
See here, there is auto built in stuff to do every DB as well: https://stackoverflow.com/questions/4726899/while-loop-to-iterate-through-databases There are few different ways to do it explained here. — Brad, Mar 05 '21 at 21:00
I'm not currently ignoring other system databases, but I placed a print statement immediately after the SET statement in the loop to identify what database is causing the problem. None have been system databases. — Joe G, Mar 05 '21 at 21:03
Ok, but you are still doing the greater than check on a DBName, change to use DBID (like in the link above). Then you may see system DB's and other stuff. — Brad, Mar 05 '21 at 21:04
The code works as is all three of the environments I tried it in (SQL Server 12, 16, and 19) All databases are first loaded into @Database (DbName) prior to looping through it — Joe G, Mar 05 '21 at 21:07
I incorporated sp_msforeachtable into the solution from the link provided (Marian's answer utilizing the DBID), and I'm still getting the same "Could not find stored procedure 'sp_msforeachtable' error after the 57th database. Again, I verified that it exists at this database and I also excluded this one and it happens at the next one — Joe G, Mar 05 '21 at 21:35

score 14 · Answer 1 · answered Oct 27 '21 at 16:02

14

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.

answered Oct 27 '21 at 16:02

Talha Tayyab

8,111
25
27
44

1

Great point! That isn't something that applied in my case, but good to keep in mind. Thanks! – Joe G Dec 07 '21 at 14:14
1

to close the loop on this answer, it'd be great to know how you might "create one for yourself" ... thanks! – beep_check Feb 04 '22 at 02:56
Example implementation of `sp_MSforeachtable` for Azure SQL can be found here: https://gist.github.com/metaskills/893599 – n-verbitsky May 10 '22 at 12:55

score 5 · Accepted Answer · answered Mar 19 '21 at 14:13

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

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

2 Answers2