mssql: enable compression

mssql-enable-compression.sql

CREATE TRIGGER [data_compression] 

ON ALL SERVER 
AFTER CREATE_TABLE 
AS 
DECLARE @SchemaName nvarchar(150),

@ObjectName nvarchar(150),
@DatabaseName nvarchar(150),
@cmd nvarchar(500)

SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
set @cmd = 'ALTER TABLE [' + @DatabaseName + '].[' + @SchemaName + '].[' + @ObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
EXEC (@cmd) 


---

CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX

AS
DECLARE @SchemaName nvarchar(150),
@ObjectName nvarchar(150),
@TargetObjectName nvarchar(150),

@DatabaseName nvarchar(150),
@cmd nvarchar(500)

SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')
SET @TargetObjectName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(150)')
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
set @cmd = 'ALTER INDEX [' + @ObjectName + '] ON [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TargetObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
EXEC (@cmd)

---

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
GO

EXEC sp_MSforeachtable 'ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
GO