Re-writing my usual defrag procedure for SQL AZURE

My usual IndexRebuild wouldnt work on SQL Azure for two reasons:

• Absence of sys.partitions
• SQL Azure doesn't take SELECT INTO (I was using that in my usual Rebuild Proc)

Working around that, I came up with this:

ALTER PROC [dbo].[Spdefragallindex] 
AS 
  BEGIN 
      CREATE TABLE #tmpfrag 
        ( 
           tbname VARCHAR(200) 
        ) 

      INSERT INTO #tmpfrag 
      SELECT TOP 20 Schema_name(o.schema_id) + '.' 
                    + Object_name(ps.object_id) AS TableName 
      FROM   sys.dm_db_partition_stats ps 
             INNER JOIN sys.indexes i 
                     ON ps.object_id = i.object_id 
                        AND ps.index_id = i.index_id 
             INNER JOIN sys.objects o 
                     ON ( i.object_id = o.object_id ) 
             CROSS apply sys.Dm_db_index_physical_stats(Db_id(), ps.object_id, 
                         ps.index_id, 
                         NULL, 
                         'LIMITED') ips 
      GROUP  BY Schema_name(o.schema_id) + '.' 
                + Object_name(ps.object_id) 
      ORDER  BY Avg(ips.avg_fragmentation_in_percent) DESC 

      DECLARE @TableName VARCHAR(255) 
      DECLARE tablecursor CURSOR FOR 
        (SELECT * 
         FROM   #tmpfrag) 

      OPEN tablecursor 

      FETCH next FROM tablecursor INTO @TableName 

      WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT( 'Rebuilding Indexes on ' + @TableName ) 

            BEGIN try 
                EXEC('ALTER INDEX ALL ON ' + @TableName + 
                ' REBUILD with (ONLINE=ON)') 
            --PRINT 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)' 
            END try 

            BEGIN catch 
                PRINT( 'Rebuild with Online=On ' + @TableName 
                       + ' UnSuccessful, removing ONLINE' ) 

                EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') 
            END catch 

            FETCH next FROM tablecursor INTO @TableName 
        END 

      CLOSE tablecursor 

      DEALLOCATE tablecursor 

      DROP TABLE #tmpfrag 

      INSERT INTO logrebuild 
                  (desclogrebuild) 
      VALUES      ('Sucesso') 

      SELECT Cast(1 AS INT) AS [Resultado] 
  END 

go 

It was based on another blog post on MSDN: http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx

This is not the actual procedure I use for REBUILDING on SQL SERVER, that will be another post... :)

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC