Post reply

Dynamic SQL - Loop to partition tables

  • 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

  • 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)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply