CODEWITHSUNDEEP
sql-server
sheldon harrison
sheldon harrison

Reputation: 1

Apply compression on multiple tables at once in SQL Server?

I am using MS SQL Server 2008, I am trying to do a SELECT INTO from one database to another, however I need the new tables to be compressed with row compression after they are built.

I used a trigger before to set compression on all tables by default but that didnt work out so well because the trigger changes the schema and doesnt allow the data to be inserted. Here is what i tried:

What other method could i use to set the compression on all the tables at once? #newbie

Upvotes: 0

Views: 1078

Answers (1)

Pete Carter
Pete Carter

Reputation: 2731

You could use a script something like this:

DECLARE @SQL NVARCHAR(MAX) =
(
SELECT 'ALTER TABLE ' + Name + ' REBUILD WITH (DATA_COMPRESSION = ROW); ' AS [data()]
FROM sys.Tables
FOR XML PATH('')
)

EXEC(@SQL)

Upvotes: 1

Related Questions