How to use sp_MSforeachdb

Question

I have a code that returns all indexes with a fragmentation % greater than 30

Iwant this code to run through all my databases and add the resultset to a table I have called IndexesToRebuild

is there a way I can use the sp_MSforeachdb to run this query thoughout all databases and insert the resultset to the IndexesToRebuild table

here is the code I have so far

if(not exists(select 1 from Utility..dtlIndexesToRebuild))
    begin




        insert into utility..dtlIndexesToRebuild

        select
                    DB_NAME(),
                    dbschemas.[name], 
                    dbtables.[name], 
                    dbindexes.[name],
                    indexstats.avg_fragmentation_in_percent         

        from 
                    sys.dm_db_index_physical_stats (DB_ID(), null, null, null, null) as indexstats
                    inner join sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
                    inner join sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
                    inner join sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
                    and indexstats.index_id = dbindexes.index_id

        where   
                    indexstats.database_id = DB_ID()
                    and avg_fragmentation_in_percent > 30

    end

Answer 1

sp_msforeachdb has some "features". For something as simple as this, it'll likely be easier to simply leverage some dynamic SQL:

USE master;
GO

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF + @CRLF +
                         N'USE ' + QUOTENAME([name]) + N';' + @CRLF +
                         N'INSERT INTO utility.dbo.dtlIndexesToRebuild (DatabaseName, SchemaName, TableName, IndexName, Fragmentation)' + @CRLF + --Guessed names of your columns
                         N'SELECT DB_NAME(),' + @CRLF + 
                         N'       dbschemas.[name],' + @CRLF + 
                         N'       dbtables.[name],' + @CRLF + 
                         N'       dbindexes.[name],' + @CRLF + 
                         N'       indexstats.avg_fragmentation_in_percent' + @CRLF + 
                         N'FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats' + @CRLF + 
                         N'     INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]' + @CRLF + 
                         N'     INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]' + @CRLF + 
                         N'     INNER JOIN sys.indexes dbindexes ON dbindexes.[object_id] = indexstats.[object_id]' + @CRLF + 
                         N'                                     AND indexstats.index_id = dbindexes.index_id' + @CRLF + 
                         N'WHERE indexstats.database_id = DB_ID()' + @CRLF + 
                         N'  AND avg_fragmentation_in_percent > 30;'
                  FROM sys.databases d
                  WHERE d.database_id > 4
                    --AND d.[name] != N'utility' --Don't know if you want to skip this
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');

--PRINT @SQL; --Your best friend. Use SELECT for over 4,000 characters.

EXEC sys.sp_executesql @SQL;

Your best friend will help you debug any errors, but I've assumed the statement you supplied is valid.