Dynamic SQL - Loop to partition tables

• rwreilly2

  SSC Enthusiast

  Points: 192

  More actions

  May 31, 2018 at 6:58 pm

  #406359

  Hi All, 

  I am looking for a solution to partition a list of tables that are over a certain size to save space. Right now I have a list of the tables in the database and want to create a loop that will go through the list and replace the table name to run the code that partitions the list instead of running it 1 by 1.

   CREATE TABLE #RowCountsAndSizes_2 (TableName NVARCHAR(128),rows CHAR(11),  
   reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
   unused VARCHAR(18))

  EXEC   sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes_2 EXEC sp_spaceused ''?'' '

  SELECT  TableName,CONVERT(bigint,rows) AS NumberOfRows,
    CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM   #RowCountsAndSizes_2 
ORDER BY SizeinKB DESC, NumberOfRows DESC, TableName

  --DROP TABLE #RowCountsAndSizes  

  I would like the for loop the replace the until it has gone through the entire list that has a size greater than a ceratin amount. 
  
USE [DB NAME]
ALTER TABLE REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

  Thanks!!
  Rich

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  June 1, 2018 at 12:58 pm

  #1992698

  You'll need a cursor to iterate through the tables, 1 by 1, using dynamic SQL and sp_executeSQL,  I'm pretty sure there's no other alternative.   Not sure I would want to do this in an automated fashion, however.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)