DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			ELSE 'REORGANIZE'
		END + ';'
	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;

DECLARE @db_name varchar(50) = N'db_name',
                @table_name varchar(250) = N'db_name.dbo.tbl_name'

SELECT  IndStat.database_id, 
                IndStat.object_id, 
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name], 
                IndStat.index_id, 
                QUOTENAME(i.name) AS index_name,
                IndStat.avg_fragmentation_in_percent,
                IndStat.partition_number, 
                (SELECT count (*) FROM sys.partitions p
                        WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count 
FROM sys.dm_db_index_physical_stats
    (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS IndStat
        INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			ELSE 'REORGANIZE'
		END + ';'
	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			ELSE 'REORGANIZE'
		END + ';'
	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;