SET NOCOUNT ON

DECLARE @totaldatabase as int,
	@dbname as sysname,
	@dbcommand as nvarchar (4000)


CREATE TABLE #DBList
( DBName sysname )

INSERT INTO #DBList
SELECT [name] as [DBName]
FROM master.sys.databases AS dtb
WHERE
	(
		CAST(
			case when dtb.name in ('master','model','msdb','tempdb') then 1 
			else dtb.is_distributor end AS bit
	) = 0 AND
	CAST(isnull(dtb.source_database_id, 0) AS bit)=0) AND
	dtb.compatibility_level > 80 AND
	dtb.[state] = 0
ORDER BY [name] ASC

SELECT @totaldatabase = COUNT(1) FROM #DBList

WHILE @totaldatabase > 0
BEGIN

SELECT TOP 1 @dbname=[DBName] FROM #DBList

set @dbcommand =
'SET NOCOUNT ON

USE [' + @dbname + ']

CREATE TABLE #IndexList
( [object_name] sysname, [index_name] sysname, IsRebuild bit )


DECLARE @i as int, @total as int

DECLARE @object_name as sysname,
	@index_name as sysname,
	@isrebuild as bit,
	@rebuildcommand as nvarchar(4000)

-- ::::: List all indexes to rebuild ::::: --
set @i = 0

INSERT INTO #IndexList
SELECT
	object_name(dbi.[object_id]) [object_name]
	, si.[name] as [index_name]
	, case when dbi.avg_fragmentation_in_percent <= 30 then 0
	  else 1 end as [IsRebuild]
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, ''DETAILED'') dbi
	INNER JOIN sys.indexes si 
		ON si.[object_id] = dbi.[object_id] AND
		   si.[index_id] = dbi.index_id
	INNER JOIN  INFORMATION_schema.tables
		ON TABLE_TYPE = ''BASE TABLE''
			AND TABLE_SCHEMA <> ''MetadataSchema''
			AND object_name(dbi.[object_id]) = TABLE_NAME

WHERE	
	dbi.index_id > 0 AND
	si.[name] is not null AND
	dbi.avg_fragmentation_in_percent >= 5 AND 
	page_count > 8
	AND object_name(dbi.[object_id]) NOT IN (''Attribute'',''EntityRelationship'',''EntityIndex'')

set @rebuildcommand = ''''

SELECT @total = COUNT(1)
FROM #IndexList

WHILE (@total > 0)
BEGIN

SELECT TOP 1 @object_name = [object_name],
	@index_name = [index_name],
	@isrebuild = [IsRebuild]
FROM #IndexList

--:::: ALTER INDEX index_name ON table_name REBUILD ::::--
IF @isrebuild = 1 
	set @rebuildcommand = @rebuildcommand + 
		N''ALTER INDEX ['' + cast (@index_name as nvarchar(255)) + N''] ON ['' + cast(@object_name as nvarchar(255)) + N''] REBUILD; ''
--:::: ALTER INDEX index_name ON table_name REORGANIZE ::::--
ELSE
	set @rebuildcommand = @rebuildcommand + 
		N''ALTER INDEX ['' + cast (@index_name as nvarchar(255)) + N''] ON ['' + cast(@object_name as nvarchar(255)) + N''] REORGANIZE; '' +
		N''UPDATE STATISTICS ['' + cast(@object_name as nvarchar(255)) + ''] ( ['' + cast (@index_name as nvarchar(255)) + ''] ) WITH FULLSCAN;''


DELETE #IndexList WHERE @object_name = [object_name] AND @index_name = [index_name]

set @total = @total - 1

IF @i = 10
begin
	EXEC sp_executesql @rebuildcommand
	set @i = 0
	set @rebuildcommand = ''''
end
ELSE
	set @i = @i + 1
END

IF @rebuildcommand <> ''''
begin
	EXEC sp_executesql @rebuildcommand
	set @rebuildcommand = ''''
end

DROP TABLE #IndexList

SET NOCOUNT OFF'

exec sp_executesql @dbcommand

DELETE #DBList WHERE [DBName] = @dbname


SET @totaldatabase = @totaldatabase - 1

END


DROP TABLE #DBList


SET NOCOUNT OFF