Adding a cursor to loop through all DBs for updating stats

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  January 18, 2018 at 12:06 pm

  #331007

  All,

  I would like to add the below script into a cursor so that it loops through all the databases and updates the statistics. I am a little confused on how to get this thing working for all the DBs

  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  -- Store relevant details
SELECT
ss.name AS [schname],
st.name AS [tblname],
si.name AS [idxname],
ssi.rowcnt INTO #t_IndexUsage
FROM sys.indexes AS si
INNER JOIN sys.sysindexes AS ssi
ON si.[object_id] = ssi.id
AND si.name = ssi.name
INNER JOIN sys.tables AS st
ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas AS ss
ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0 -- Only application indexes
AND si.index_id <> 0 -- Ignore heaps
AND ssi.rowcnt > 100 -- Only indexes with at least 100 rows
AND ssi.rowmodctr > 0 -- Only indexes with changed data

  -- Build Update Statistics SQL (concatenated)
DECLARE @UpdateStatisticsSQL nvarchar(max)
SET @UpdateStatisticsSQL = ''

  SELECT
@UpdateStatisticsSQL = @UpdateStatisticsSQL + CHAR(10) + 'UPDATE STATISTICS ' + QUOTENAME(schname) + '.' + QUOTENAME(tblname) + ' ' + QUOTENAME(idxname) + ' WITH SAMPLE ' +
CASE
  WHEN rowcnt < 500000 THEN '100 PERCENT'
  WHEN rowcnt < 1000000 THEN '50 percent'
  WHEN rowcnt < 5000000 THEN '25 percent'
  WHEN rowcnt < 10000000 THEN '10 PERCENT'
  WHEN rowcnt < 50000000 THEN '2 percent'
  WHEN rowcnt < 100000000 THEN '1 percent'
  ELSE '3000000 ROWS'
END
+ '-- ' + CAST(rowcnt AS varchar(22)) + ' rows'
FROM #t_IndexUsage

  -- Debug
DECLARE @StartOffset int
DECLARE @Length int

  SET @StartOffset = 0
SET @Length = 4000

  WHILE (@StartOffset) < LEN(@UpdateStatisticsSQL)
BEGIN
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)

  -- Execute Update Statistics
EXEC sp_executesql @UpdateStatisticsSQL

  -- Tidy Up
DROP TABLE #t_IndexUsage;


• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  January 18, 2018 at 1:03 pm

  #1976409

  There are many ways to do so...
  On of them is using an undocumented procedure:

  EXEC sp_MSforeachdb @command