Updating indexes and stats script

Question

I am having the following errors with the script below

Msg 102, Level 15, State 1, Line 44
Incorrect syntax near '?'.

Msg 319, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Can anyone help to resolve the issue, it also does not seem to loop around each db

DECLARE @command    NVARCHAR(MAX)
CREATE TABLE #worktable
(
    [Database]  SYSNAME
    ,SchemaName SYSNAME
    ,ObjectName SYSNAME
    ,StatsName  SYSNAME
    ,ColName    SYSNAME
    --,Command  VARCHAR(500)
)

exec [master].[sys].sp_MSForEachDB @command1="use [?]"
INSERT INTO #worktable 
    SELECT 
        '[?]'                                               AS [Database]
        ,sch.[name]                                     AS [SchemaName]
        --,OBJECT_NAME(o.[object_id],DB_ID(''[?]''))            AS [ObjectName]
        ,o.[Name]                                       AS [ObjectName]
        ,s.name                                             AS [StatsName]
        ,c.name                                             AS [ColName]
        --,''COMMAND''                                      AS [Command]
        FROM sys.stats AS s

    INNER JOIN sys.stats_columns AS sc 
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id

    INNER JOIN sys.objects o
    INNER JOIN sys.schemas AS sch
        ON o.schema_id = sch.schema_id
        ON s.[object_id] = o.[object_id]
        AND o.is_ms_shipped = 0

    INNER JOIN sys.columns AS c 
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id

select * from #worktable
drop table #worktable

exec [MASTER].[sys].sp_MSForEachDB 
INSERT INTO #worktable 
    SELECT  'Test'      AS [Database]
        ,OBJECT_SCHEMA_NAME(sch.name ,DB_ID(''[?]''))   AS [SchemaName]
        ,OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))     AS [ObjectName]
        ,ss.[name]                          AS [StatsName]
        ,''UPDATE STATISTICS ''+''[?]''+''.''+OBJECT_SCHEMA_NAME(ss.[object_id],DB_ID(''[?]''))+''.''+OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))+'' ''+ss.[name]+'' WITH FULLSCAN;'' AS Command

    FROM [?].sys.stats ss

    CROSS APPLY [?].sys.dm_db_stats_properties(ss.[object_id],ss.stats_id) sp

    WHERE   (           (sp.last_updated < DATEADD(hh,-6,GetDate()))
            OR      (sp.[modification_counter] > (0.1*sp.[rows]))
            OR      (sp.[rows_sampled] <> sp.[rows])
        )

    ORDER BY sp.[rows] ASC -- does the small stuff first, makes the difference earlier'

DECLARE cmdlist CURSOR FOR SELECT Command FROM #worktable

-- Open the cursor.
OPEN cmdlist

-- Loop through the partitions
WHILE (1=1)
   BEGIN
        FETCH NEXT FROM cmdlist
           INTO @command

        IF @@FETCH_STATUS < 0 BREAK

        --EXEC (@command);

        PRINT N'Executed: ' + @command
    END;

CLOSE cmdlist
DEALLOCATE cmdlist

DROP TABLE #worktable
GO

Answer 1

My honest opinion is that - Don't reinvent the wheel as there is cost to reinvent it.

Highly suggest you to use Ola's index and stats maintenance solution.

It is very flexible and is highly awarded and tested. Also it does not use any of ms_foreach stuff that you have in your script.

Answer 2

First let me say that it's generally recommended that you not use sp_msforeachdb. It has some well documented flaws and will occasionally miss databases. @AaronBertrand has a version he wrote that is much better.

Second you appear to be cutting off your string early. The way you have it written it's going to execute like this.

This part will execute against each DB first:

exec [master].[sys].sp_MSForEachDB @command1="use [?]"

Then this part will execute against your current DB:

INSERT INTO #worktable 
    SELECT 
        '[?]'                                               AS [Database]
        ,sch.[name]                                     AS [SchemaName]
        --,OBJECT_NAME(o.[object_id],DB_ID(''[?]''))            AS [ObjectName]
        ,o.[Name]                                       AS [ObjectName]
        ,s.name                                             AS [StatsName]
        ,c.name                                             AS [ColName]
        --,''COMMAND''                                      AS [Command]
        FROM sys.stats AS s

    INNER JOIN sys.stats_columns AS sc 
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id

    INNER JOIN sys.objects o
    INNER JOIN sys.schemas AS sch
        ON o.schema_id = sch.schema_id
        ON s.[object_id] = o.[object_id]
        AND o.is_ms_shipped = 0

    INNER JOIN sys.columns AS c 
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id

My guess is that you really want the first part of your code to look like this:

DECLARE @sql nvarchar(4000)

SET @SQL = N'use [?];
    INSERT INTO #worktable 
        SELECT 
            ''[?]''                                               AS [Database]
            ,sch.[name]                                     AS [SchemaName]
            --,OBJECT_NAME(o.[object_id],DB_ID(''[?]''))            AS [ObjectName]
            ,o.[Name]                                       AS [ObjectName]
            ,s.name                                             AS [StatsName]
            ,c.name                                             AS [ColName]
            --,''COMMAND''                                      AS [Command]
            FROM sys.stats AS s

        INNER JOIN sys.stats_columns AS sc 
            ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id

        INNER JOIN sys.objects o
        INNER JOIN sys.schemas AS sch
            ON o.schema_id = sch.schema_id
            ON s.[object_id] = o.[object_id]
            AND o.is_ms_shipped = 0

        INNER JOIN sys.columns AS c 
            ON sc.object_id = c.object_id AND c.column_id = sc.column_id'

exec [master].[sys].sp_MSForEachDB @SQL

You have a similar problem in the second half of your script. You want to declare a variable, put your command into it, then run sp_MSForEachDB against the variable. This way you can print out your variable first to make sure you are running the correct command.