-- Source: http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

/*
  List all indexes in DB
*/
SELECT SCHEMA_NAME(o.schema_id)     AS SchemaName,
       OBJECT_NAME(a.object_id)     AS TableName,
       i.name                       AS IndexName,
       a.index_id                   AS IndexID,
       convert(tinyint,a.avg_fragmentation_in_percent) AS [Fragment%]
FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL,
       NULL, 'LIMITED') AS a
       INNER JOIN sys.indexes i
         ON i.index_id = a.index_id
         AND i.object_id = a.object_id 
       INNER JOIN sys.objects o
         ON a.object_id = o.object_id
ORDER BY [Fragment%] desc
         -- SchemaName, TableName, IndexID


/*
	REBUILD (ONLINE) all indexes in DB
*/
DECLARE @DatabaseName SYSNAME   = DB_NAME(), @TableName VARCHAR(256), @TableSchema VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
 'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA AS TABLESCHEMA, TABLE_NAME AS TABLENAME  
 FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES WHERE
 TABLE_TYPE = ''BASE TABLE'''  
BEGIN 
  EXEC sp_executeSQL @DynamicSQL  -- create tables cursor
  OPEN curAllTablesInDB  
  FETCH NEXT FROM curAllTablesInDB INTO @TableSchema, @TableName  
  WHILE (@@FETCH_STATUS = 0) 
  BEGIN  
       SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableSchema + '.[' + @TableName + ']' +
         ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')' 
       -- PRINT @DynamicSQL
       EXEC sp_executeSQL @DynamicSQL 
       FETCH NEXT FROM curAllTablesInDB INTO @TableSchema, @TableName  
   END   -- cursor WHILE
   CLOSE curAllTablesInDB  
   DEALLOCATE curAllTablesInDB
END