SET NOCOUNT ON;

DECLARE @DatabaseName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
  AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '--- İşlem Başladı: ' + @DatabaseName;
    
    SET @SQL = '
    USE [' + @DatabaseName + '];
    DECLARE @SchemaName NVARCHAR(255);
    DECLARE @TableName NVARCHAR(255);
    DECLARE @IndexName NVARCHAR(255);
    DECLARE @FragPercent FLOAT;
    DECLARE @PageCount INT;
    DECLARE @DynSQL NVARCHAR(MAX);

    DECLARE index_cursor CURSOR FOR
    SELECT 
        s.name AS SchemaName,
        t.name AS TableName,
        i.name AS IndexName,
        ips.avg_fragmentation_in_percent,
        ips.page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') AS ips
    INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    INNER JOIN sys.tables AS t ON i.object_id = t.object_id
    INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
    WHERE ips.page_count > 100 
      AND i.type_desc IN (''CLUSTERED'', ''NONCLUSTERED'') 
      AND i.name IS NOT NULL;

    OPEN index_cursor;
    FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @FragPercent, @PageCount;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @FragPercent BETWEEN 5 AND 30
        BEGIN
            SET @DynSQL = ''ALTER INDEX ['' + @IndexName + ''] ON ['' + @SchemaName + ''].['' + @TableName + ''] REORGANIZE;'';
            PRINT ''--> Reorganize: '' + @DynSQL;
            EXEC (@DynSQL);
        END
        ELSE IF @FragPercent > 30
        BEGIN
            SET @DynSQL = ''ALTER INDEX ['' + @IndexName + ''] ON ['' + @SchemaName + ''].['' + @TableName + ''] REBUILD WITH (FILLFACTOR = 90);'';
            PRINT ''--> Rebuild: '' + @DynSQL;
            EXEC (@DynSQL);
        END

        FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @FragPercent, @PageCount;
    END

    CLOSE index_cursor;
    DEALLOCATE index_cursor;
    ';

    EXEC sp_executesql @SQL;

    PRINT '--- Bitti: ' + @DatabaseName;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
