Sql-server – Updating indexes and stats script

index-tuningsql servert-sql

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

Best Answer

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.