ReIndex all tables and Views in Sql Server without any blocks

If you are trying to rebuild all indexes on a Database without any locks or with minimum downtime then use the below script. This script which is a stored procedure will let you specify the Database Name and the Minium Fragmentation percent of the index you would like to rebuild.

Note: Microsoft has specified the INDEXDEFRAG syntax will be replaced with Alter Index in future versions of Sql server and not right now.


CREATE PROCEDURE [dbo].[ReBuildIdx]
@DBName nvarchar(max),
@FragPercent int
AS
DECLARE @TableName sysname
DECLARE @indid int
DECLARE @indname nvarchar(max)
DECLARE @indFraPer int
declare @sql nvarchar(max)
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type in('base table','view')
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid,name FROM sysindexes WHERE id = OBJECT_ID (@TableName) and rows > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid, @indname
WHILE @@FETCH_STATUS = 0
BEGIN
Select top 1 @indFraPer= avg_fragmentation_in_percent from sys.dm_db_index_physical_stats (db_id(@DBName),OBJECT_ID (@TableName),@indid,NULL,'SAMPLED')

IF (@indid <> 255 and @indFraPer>@FragPercent)
BEGIN
select CAST(GETDATE() as varchar) +' Derfagmenting index = ' + @indname + 'of the '+ rtrim(@TableName) + ' table'
set @sql = 'DBCC INDEXDEFRAG ('+@DBName+', '+@TableName+', '+CAST(@indid as nvarchar(max))+')'
--print @sql
exec(@sql)
END
FETCH NEXT FROM cur_indfetch INTO @indid,@indname
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch