SHARE
TWEET

Untitled

a guest Apr 25th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE
  2. @table_name NVARCHAR(500)
  3. ,@schema_name NVARCHAR(500)
  4. ,@query NVARCHAR(500)
  5.  
  6. DECLARE db_cursor CURSOR FOR
  7. SELECT
  8.     t.NAME AS [table_name]
  9.     ,s.NAME AS [schema_name]
  10. FROM sys.tables t
  11. INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
  12. INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  13. INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  14. WHERE
  15.     t.NAME NOT LIKE 'dt%'
  16.     AND i.OBJECT_ID > 255
  17. GROUP BY
  18.     t.NAME, s.name
  19. HAVING MAX([data_compression_desc]) NOT IN ('PAGE','ROW')
  20.  
  21. OPEN db_cursor  
  22. FETCH NEXT FROM db_cursor INTO @table_name, @schema_name
  23.  
  24. WHILE @@FETCH_STATUS = 0
  25. BEGIN
  26.     BEGIN TRY
  27.         SET @query = N'ALTER TABLE [' + @schema_name + N'].[' + @table_name + N'] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE )'
  28.         PRINT @query
  29.         EXEC sp_executesql @query
  30.     END TRY
  31.     BEGIN CATCH
  32.         RAISERROR( 'Compression failed for backup table : %s', 20, 101, @table_name ) WITH LOG
  33.     END CATCH
  34.  
  35.     FETCH NEXT FROM db_cursor INTO @table_name, @schema_name
  36. END
  37.  
  38. CLOSE db_cursor  
  39. DEALLOCATE db_cursor
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top