-- Kayit sayisina gore, coga dogru compress ederek calisiyor -- Boylece, yer acildikca, daha buyuk tablolar daha rahat compress edilebilecekler.
DECLARE @sqlcmd nvarchar(max), @rowcount int, @size int
DECLARE compress_cursor CURSOR FOR SELECT 'ALTER TABLE ' + '[' + T.TABLE_SCHEMA + ']'+'.' + '[' + t.TABLE_NAME + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE); GO PRINT ''TABLO ADI: '+t.TABLE_NAME+' COMPRESS ISLEMI BİTTİ'' GO', i.rowcnt, CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB FROM sysindexes AS i INNER JOIN sysobjects AS o ON i.id = o.id INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name INNER JOIN sys.partitions p ON i.id = p.object_id INNER JOIN sys.allocation_units a ON p.partition_id=a.container_id WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 AND i.rowcnt >10 AND T.TABLE_TYPE='BASE TABLE' GROUP BY T.TABLE_SCHEMA,t.TABLE_NAME,i.rowcnt ORDER BY Total_MB ASC
OPEN compress_cursor; FETCH NEXT FROM compress_cursor INTO @sqlcmd,@rowcount,@size WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONCAT('--KAYIT SAYISI: ',CONVERT(CHAR(15),@rowcount)) PRINT CONCAT('--TABLO BOYUTU: ' ,+@size)+' MB' PRINT @sqlcmd PRINT'--------------------------------------------------------' -- EXEC sp_executesql @sqlcmd FETCH NEXT FROM compress_cursor INTO @sqlcmd,@rowcount,@size END CLOSE compress_cursor DEALLOCATE compress_cursor
