SHARE
TWEET

Untitled

a guest Jun 26th, 2019 79 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ABC_XXXX
  2. ABC_YYYY
  3. DEF_ZZZZ
  4. DEF_TTTT
  5. GHI_UUUU
  6.      
  7. CREATE (UNIQUE|CLUSTERED|) INDEX <Index Name> ON <Table Name>(<Index Columns>)
  8.        WITH (DROP_EXISTING = ON) ON <New Filegroup>
  9.      
  10. SET NOCOUNT ON;
  11.  
  12. DECLARE @sql NVARCHAR(MAX);
  13.  
  14. SET @sql = N'';
  15.  
  16. ;WITH src AS
  17. (
  18.   SELECT
  19.     obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
  20.     + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
  21.     i.[object_id],
  22.     i.index_id,
  23.     i.name,
  24.     uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
  25.     type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
  26.     ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
  27.     dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
  28.         WHEN 'ABC' THEN 'FG1'
  29.         WHEN 'DEF' THEN 'FG2'
  30.         ELSE 'DEFAULT'
  31.         END
  32.   FROM sys.indexes AS i
  33.   INNER JOIN sys.partitions AS p
  34.   ON i.[object_id] = p.[object_id]
  35.   AND i.index_id = p.index_id
  36.   WHERE i.index_id > 0
  37.   -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
  38. ),
  39. cols AS
  40. (
  41.   SELECT
  42.     name = QUOTENAME(c.name),
  43.     ic.key_ordinal,
  44.     ic.[object_id],
  45.     ic.index_id,
  46.     sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
  47.     ic.is_included_column
  48.   FROM sys.index_columns AS ic
  49.   INNER JOIN sys.columns AS c
  50.   ON ic.[object_id] = c.[object_id]
  51.   AND ic.column_id = c.column_id
  52.   WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
  53. )
  54. SELECT @sql = @sql + CHAR(13) + CHAR(10)
  55.     + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
  56.     + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
  57.         WHERE cols.object_id = src.object_id
  58.         AND cols.index_id = src.index_id
  59.         AND cols.is_included_column = 0
  60.         ORDER BY cols.key_ordinal
  61.         FOR XML PATH('')), 1, 1, '') + ')'
  62.     + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
  63.         WHERE cols.[object_id] = src.[object_id]
  64.         AND cols.index_id = src.index_id
  65.         AND cols.is_included_column = 1
  66.         ORDER BY cols.key_ordinal
  67.         FOR XML PATH('')), 1, 1, '') + ')', '')
  68.     + ' WITH (DROP_EXISTING = ON' + ff
  69.     + ') ON ' + dest + ';'
  70.   FROM src
  71.   ORDER BY uniq DESC, type_desc;
  72.  
  73. SELECT @sql;
  74. -- EXEC sp_executesql @sql;
  75.      
  76. SET NOCOUNT ON;
  77.  
  78. DECLARE @sql NVARCHAR(MAX);
  79.  
  80. SET @sql = N'';
  81.  
  82. ;WITH src AS
  83. (
  84.   SELECT
  85.     obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
  86.     + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
  87.     i.[object_id],
  88.     i.index_id,
  89.     i.name,
  90.     uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
  91.     type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
  92.     filter = CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END,
  93.     ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
  94.     dc = CASE p.data_compression_desc WHEN 'NONE' THEN ''
  95.         ELSE ', DATA_COMPRESSION = ' + p.data_compression_desc END,
  96.     dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
  97.         WHEN 'ABC' THEN 'FG1'
  98.         WHEN 'DEF' THEN 'FG2'
  99.         ELSE 'DEFAULT'
  100.         END
  101.   FROM sys.indexes AS i
  102.   INNER JOIN sys.partitions AS p
  103.   ON i.[object_id] = p.[object_id]
  104.   AND i.index_id = p.index_id
  105.   WHERE i.index_id > 0
  106.   -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
  107. ),
  108. cols AS
  109. (
  110.   SELECT
  111.     name = QUOTENAME(c.name),
  112.     ic.key_ordinal,
  113.     ic.[object_id],
  114.     ic.index_id,
  115.     sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
  116.     ic.is_included_column
  117.   FROM sys.index_columns AS ic
  118.   INNER JOIN sys.columns AS c
  119.   ON ic.[object_id] = c.[object_id]
  120.   AND ic.column_id = c.column_id
  121.   WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
  122. )
  123. SELECT @sql = @sql + CHAR(13) + CHAR(10)
  124.     + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
  125.     + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
  126.         WHERE cols.object_id = src.object_id
  127.         AND cols.index_id = src.index_id
  128.         AND cols.is_included_column = 0
  129.         ORDER BY cols.key_ordinal
  130.         FOR XML PATH('')), 1, 1, '') + ')'
  131.     + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
  132.         WHERE cols.[object_id] = src.[object_id]
  133.         AND cols.index_id = src.index_id
  134.         AND cols.is_included_column = 1
  135.         ORDER BY cols.key_ordinal
  136.         FOR XML PATH('')), 1, 1, '') + ')', '')
  137.     + filter + ' WITH (DROP_EXISTING = ON' + ff + dc
  138.     + ') ON ' + dest + ';'
  139.   FROM src
  140.   ORDER BY uniq DESC, type_desc;
  141.  
  142. SELECT @sql;
  143. -- EXEC sp_executesql @sql;
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