將大量表移動到不同的文件組

現有數據庫在 PRIMARY 文件組中儲存了大量表。我想根據表名的“前綴”自動將這些表及其索引移動到不同的文件組上。

例如，有 5 個這樣命名的表：

ABC_XXXX
ABC_YYYY
DEF_ZZZZ
DEF_TTTT
GHI_UUUU

所有以 開頭的表ABC都應該移到 filegroup FG1，DEF到 filegroupFG2和其他表到 filegroup DEFAULT。

這可以使用以下命令完成CREATE INDEX：

CREATE (UNIQUE|CLUSTERED|) INDEX <Index Name> ON <Table Name>(<Index Columns>)
      WITH (DROP_EXISTING = ON) ON <New Filegroup>

此命令的最大問題是按正確順序檢索每個索引的列。

您在描述中遺漏了很多內容，腳本需要適應這些內容 - 索引是主鍵還是唯一約束？是否有任何列降序？索引是否被過濾？它有任何 INCLUDE 列嗎？雖然您當然可以手動生成腳本，但為什麼不使用生成腳本嚮導呢？

1. 在對象資源管理器中，右鍵點擊您的數據庫
2. 選擇任務 > 生成腳本…
3. 點擊下一步
4. 選擇“選擇特定數據庫對象”，然後選擇所有名為 ABC 的表…
5. 點擊下一步
6. 點擊高級
7. 向下滾動並將“腳本索引”更改為 True
8. 點擊確定
9. 將選項更改為“保存到新的查詢視窗”
10. 點擊下一步/下一步/完成

現在您有一個包含所有腳本的查詢視窗，您必須刪除一些腳本並對其他腳本進行一些手動按摩，但是對於不屬於 PK 的索引，您應該能夠簡單地搜尋和替換DROP_EXISTING = OFF到DROP_EXISTING = ON然後[PRIMARY]換成FG1…

這是一個使用sys.indexes,sys.columns和sys.index_columns- 的腳本，它在存在顯式主鍵的情況下創建唯一索引（因為您沒有提供DROP_EXISTING我要求的“使用”語法重新創建 PK）。這涉及包含列、填充因子、以正確的順序放置列，甚至確保首先建構唯一/聚集索引。

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
 SELECT 
   obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
   + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
   i.[object_id],
   i.index_id,
   i.name,
   uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
   type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
   ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
   dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
       WHEN 'ABC' THEN 'FG1'
       WHEN 'DEF' THEN 'FG2'
       ELSE 'DEFAULT'
       END
 FROM sys.indexes AS i
 INNER JOIN sys.partitions AS p
 ON i.[object_id] = p.[object_id]
 AND i.index_id = p.index_id
 WHERE i.index_id > 0
 -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
 SELECT
   name = QUOTENAME(c.name),
   ic.key_ordinal,
   ic.[object_id],
   ic.index_id,
   sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
   ic.is_included_column
 FROM sys.index_columns AS ic
 INNER JOIN sys.columns AS c
 ON ic.[object_id] = c.[object_id]
 AND ic.column_id = c.column_id
 WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10) 
   + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
   + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols 
       WHERE cols.object_id = src.object_id 
       AND cols.index_id = src.index_id
       AND cols.is_included_column = 0
       ORDER BY cols.key_ordinal 
       FOR XML PATH('')), 1, 1, '') + ')' 
   + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols 
       WHERE cols.[object_id] = src.[object_id] 
       AND cols.index_id = src.index_id
       AND cols.is_included_column = 1
       ORDER BY cols.key_ordinal 
       FOR XML PATH('')), 1, 1, '') + ')', '') 
   + ' WITH (DROP_EXISTING = ON' + ff
   + ') ON ' + dest + ';'
 FROM src
 ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

為了完整起見，這裡有一個腳本，它添加了sys.partitions幾個 SQL Server 2008 特定的功能，例如過濾索引和數據壓縮。

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
 SELECT 
   obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
   + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
   i.[object_id],
   i.index_id,
   i.name,
   uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
   type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
   filter = CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END,
   ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
   dc = CASE p.data_compression_desc WHEN 'NONE' THEN '' 
       ELSE ', DATA_COMPRESSION = ' + p.data_compression_desc END,
   dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
       WHEN 'ABC' THEN 'FG1'
       WHEN 'DEF' THEN 'FG2'
       ELSE 'DEFAULT'
       END
 FROM sys.indexes AS i
 INNER JOIN sys.partitions AS p
 ON i.[object_id] = p.[object_id]
 AND i.index_id = p.index_id
 WHERE i.index_id > 0
 -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
 SELECT
   name = QUOTENAME(c.name),
   ic.key_ordinal,
   ic.[object_id],
   ic.index_id,
   sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
   ic.is_included_column
 FROM sys.index_columns AS ic
 INNER JOIN sys.columns AS c
 ON ic.[object_id] = c.[object_id]
 AND ic.column_id = c.column_id
 WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10) 
   + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
   + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols 
       WHERE cols.object_id = src.object_id 
       AND cols.index_id = src.index_id
       AND cols.is_included_column = 0
       ORDER BY cols.key_ordinal 
       FOR XML PATH('')), 1, 1, '') + ')' 
   + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols 
       WHERE cols.[object_id] = src.[object_id] 
       AND cols.index_id = src.index_id
       AND cols.is_included_column = 1
       ORDER BY cols.key_ordinal 
       FOR XML PATH('')), 1, 1, '') + ')', '') 
   + filter + ' WITH (DROP_EXISTING = ON' + ff + dc
   + ') ON ' + dest + ';'
 FROM src
 ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

引用自：https://dba.stackexchange.com/questions/16708