Moving indices to a different partition in the Microsoft SQL Server

July 1st, 2007

Let’s say your database grows big and you need to add additional storage. You can engage in a re-partitioning project using a backup/restore strategy, but the simplest thing to do is to start with moving indices. Here is the script to do that:

CREATE TABLE #foo
(
   table_name VARCHAR(255),
   index_name VARCHAR(255),
   columns    VARCHAR(255),
)
INSERT INTO #fooSELECT tbl.name AS table_name, idx.name AS index_name,
       ISNULL('' +INDEX_COL( tbl.name, idx.indid,  1),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  2),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  3),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  4),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  5),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  6),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  7),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  8),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid,  9),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 10),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 11),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 12),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 13),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 14),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 15),'')+
       ISNULL(','+INDEX_COL( tbl.name, idx.indid, 16),'')
       AS columns
  FROM SYSINDEXES idx
  JOIN SYSOBJECTS tbl
    ON idx.id = tbl.id
   AND tbl.type = 'U'
   AND idx.name NOT LIKE '_WA_Sys_%'
   AND idx.name NOT LIKE 'PK_%'
   AND idx.name NOT LIKE 'IX_%'
   AND INDEX_COL( tbl.name, idx.indid,  1) IS NOT NULL
 ORDER BY tbl.name, idx.name
SELECT 'DROP INDEX ['+table_name+'].[' + index_name + ']'+ CHAR(10)+
          'CREATE INDEX ['+index_name+'] ON ['+table_name+']('+columns+') ON [INDEXES]'+ CHAR(10)
  FROM #foo
DROP TABLE #foo

It produces a script that will drop all indices from the current database and will recreate the same on a different partition, in this case, the one called [INDEXES]. So, paste the result in your favorite SQL interface program and execute it.

Posted by Ivan K Filed in Programming