March 12, 2015 at 4:02 pm
Hi
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 @commandNVARCHAR(MAX)
CREATE TABLE #worktable
(
[Database]SYSNAME
,SchemaNameSYSNAME
,ObjectNameSYSNAME
,StatsNameSYSNAME
,ColNameSYSNAME
--,CommandVARCHAR(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.nameAS [StatsName]
,c.nameAS [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
-- populate worktable with stats older than 6 hours (avoids "hot" stats and recently-generated stats), has colmodctr > 10% , or rows_sampled < rowcount
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
March 12, 2015 at 11:28 pm
March 13, 2015 at 5:49 am
I'd suggest you also check out Minion Reindex by the Midnight DBA team. It's a great tool. I wrote a review of it here[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 7:00 am
Another great option is Ola.Hallengren.com. His AWESOME, FREE, DOCUMENTED stuff offers up the full range of maintenance needs too!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 13, 2015 at 1:25 pm
Don't take this bad but being blatant and straight to the point, given the lack of quality and the level of naivety portrait in the code posted, I strongly recommend that you use proven solutions like Ola's or the Minion Reindex, personally I prefer Ola's scripts as I have never had any problem with them, they are well documented and in constant development/improvement.
😎
March 13, 2015 at 1:32 pm
Thanks for the responses.
Will look at Ola's scripts
🙂
March 13, 2015 at 4:18 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply