Компрессия пользовательских таблиц

IF object_id(N'tempdb..#tblResults', N'u') IS NOT NULL
    DROP TABLE #tblResults

IF object_id(N'tempdb..#tblResults1', N'u') IS NOT NULL
    DROP TABLE #tblResults1

-- Create the temporary table...
CREATE TABLE #tblResults (
    [name] NVARCHAR(60)
    ,[rows] INT
    ,[reserved] VARCHAR(18)
    ,[reserved_int] INT DEFAULT(0)
    ,[data] VARCHAR(18)
    ,[data_int] INT DEFAULT(0)
    ,[index_size] VARCHAR(18)
    ,[index_size_int] INT DEFAULT(0)
    ,[unused] VARCHAR(18)
    ,[unused_int] INT DEFAULT(0)
    )

CREATE TABLE #tblResults1 (
    [name] NVARCHAR(60)
    ,[rows] INT
    ,[reserved] VARCHAR(18)
    ,[data] VARCHAR(18)
    ,[index_size] VARCHAR(18)
    ,[unused] VARCHAR(18)
    )

DECLARE @tabname VARCHAR(100)

DECLARE cur CURSOR
FOR
SELECT TOP 1000 s.NAME + '.' + t.NAME
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE [t].[type] = 'U'

OPEN cur

FETCH NEXT
FROM cur
INTO @tabname

WHILE @@fetch_status >= 0
BEGIN
    TRUNCATE TABLE #tblResults1

    INSERT INTO #tblResults1 (
        [name]
        ,[rows]
        ,[reserved]
        ,[data]
        ,[index_size]
        ,[unused]
        )
    EXEC sp_spaceused @tabname

    INSERT INTO #tblResults (
        [name]
        ,[rows]
        ,[reserved]
        ,[data]
        ,[index_size]
        ,[unused]
        )
    SELECT TOP 1 @tabname
        ,[rows]
        ,[reserved]
        ,[data]
        ,[index_size]
        ,[unused]
    FROM #tblResults1

    FETCH NEXT
    FROM cur
    INTO @tabname
END

CLOSE cur

DEALLOCATE cur

UPDATE #tblResults
SET [reserved_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS INT)
    ,[data_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS INT)
    ,[index_size_int] = CAST(SUBSTRING([index_size], 1, CHARINDEX(' ', [index_size])) AS INT)
    ,[unused_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS INT)

SELECT *
FROM #tblResults
ORDER BY reserved_int DESC

--В определении элементов для обработки в курсоре, можно задать критерии отбора таблиц 
--для компрессии, как например в данном примере это 10 наиболее тяженовесных таблиц.
DECLARE cur CURSOR
FOR
SELECT TOP 10 NAME
FROM #tblResults
ORDER BY reserved_int DESC

OPEN cur

FETCH NEXT
FROM cur
INTO @tabname

WHILE @@fetch_status >= 0
BEGIN
    PRINT 'compressed ' + + @tabname

    EXEC (
            'ALTER TABLE ' + @tabname + ' REBUILD PARTITION = ALL

    WITH (DATA_COMPRESSION = PAGE)'
            )

    FETCH NEXT
    FROM cur
    INTO @tabname
END

CLOSE cur

DEALLOCATE cur

Используем sqlcmd mode в SSMS