CODEWITHSUNDEEP
sql-servert-sql
Mark
Mark

Reputation: 570

How do I set compression to "none" for all of the tables in a SQL Server database

I need to set the DataCompressionOptions to None for all of the tables in a database.

I can run

USE [MyDatabase]

ALTER TABLE [dbo].[Tablex] 
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE)

And it works fine but I need to set this for about 50 tables.

Upvotes: 1

Views: 2976

Answers (3)

Mariusz Hawryński
Mariusz Hawryński

Reputation: 1

-- tables

EXEC sp_MSforeachtable 'ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)'

GO

-- indexes

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)'

GO

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try this 

DECLARE @sql NVARCHAR(max)=''

SELECT @sql = @sql + 'ALTER TABLE ' + QUOTENAME(s.name) + '.'
              + QUOTENAME(t.name)
              + ' REBUILD PARTITION = ALL  WITH  (DATA_COMPRESSION = NONE )'
              + char(10) + char(13)
FROM   sys.tables t
       INNER JOIN sys.schemas s
               ON s.schema_id = t.schema_id
WHERE  T.NAME IN ( '<table_list>' )

EXEC sp_executesql
  @sql

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

USE [MyDatabase]
GO

DECLARE @Sql NVARCHAR(MAX);
DECLARE @cur CURSOR;

SET @Cur = CURSOR FOR
SELECT 'ALTER TABLE ' + QUOTENAME(s.name)+ '.' + QUOTENAME(t.name) 
        + ' REBUILD PARTITION = ALL  WITH  (DATA_COMPRESSION = NONE )'
FROM sys.tables t 
INNER JOIN sys.schemas s  ON s.schema_id = t.schema_id 

OPEN @cur

FETCH NEXT FROM @Cur INTO @Sql 

WHILE (@@FETCH_STATUS = 0)
BEGIN

    EXEC sp_executesql @Sql 

   FETCH NEXT FROM @Cur INTO @Sql 
END

CLOSE @cur
DEALLOCATE @cur

Upvotes: 2

Related Questions