CREATE procedure [dbo].[sp_getIndexes]
@move_to VARCHAR(1000) = NULL, --which filegroup are you moving index to?
@table VARCHAR(1000) = '%',
@schema VARCHAR(1000) = '%'
AS
SELECT
a.object_id,
a.index_id,
'CREATE ' +
CASE is_unique WHEN 1 THEN ' UNIQUE ' ELSE + '' END +
a.type_desc +
' INDEX ' +
QUOTENAME(a.name) COLLATE DATABASE_DEFAULT +
' ON ' +
QUOTENAME(c.name) + '.' +
QUOTENAME(b.name) +
e.index_cols +
ISNULL(f.included_cols,'') +
CASE WHEN filter_definition IS NOT NULL THEN ' WHERE ' + filter_definition +
')' ELSE '' END +
' WITH (FILLFACTOR='+
CAST(fill_factor AS VARCHAR(5)) +
' ,SORT_IN_TEMPDB = ON, DROP_EXISTING = ON, DATA_COMPRESSION = '+ data_compression_desc +')' +
' ON ' +
quotename(ISNULL(@move_to,d.name)) AS create_script
FROM sys.indexes a (NOLOCK)
INNER JOIN sys.all_objects b (NOLOCK)on a.object_id = b.object_id
inner join sys.schemas c (NOLOCK)on c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d (NOLOCK)on d.data_space_id = a.data_space_id
INNER JOIN (
SELECT DISTINCT a.object_id,index_id ,
'(' + STUFF((
SELECT ',' + QUOTENAME([name]) + CASE a.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END as [text()]
FROM sys.columns b (NOLOCK)
INNER JOIN sys.index_COLUMNS c on c.column_id = b.column_id and b.object_id = c.object_id
WHERE a.object_id = b.object_id and c.index_id = a.index_id and c.is_included_column = 0
ORDER BY c.key_ordinal
FOR XML PATH ('')),1,1,'') + ')' index_cols
FROM sys.index_COLUMNS a (NOLOCK)
) e on e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN (
SELECT DISTINCT a.object_id,index_id ,
' INCLUDE (' + STUFF((
SELECT ',' + QUOTENAME([name]) as [text()]
FROM sys.columns b (NOLOCK)
INNER JOIN sys.index_COLUMNS c on c.column_id = b.column_id and b.object_id = c.object_id
WHERE a.object_id = b.object_id and c.index_id = a.index_id and c.is_included_column = 1
ORDER BY a.key_ordinal
FOR XML PATH ('')),1,1,'') + ')' included_cols
FROM sys.index_COLUMNS a (NOLOCK)
) f on f.object_id = a.object_id AND f.index_id = a.index_id
INNER JOIN sys.partitions g (NOLOCK) on g.object_id = a.object_id and g.index_id = a.index_id
where a.index_id >0 and
c.name != 'sys' and
b.name like '%'+@table +'%'and
c.name like '%'+@schema +'%'
UNION ALL
SELECT
a.object_id,
a.index_id,
'CREATE ' +
CASE is_unique WHEN 1 THEN ' UNIQUE ' ELSE + '' END +
a.type_desc +
' INDEX ' +
QUOTENAME(a.name) COLLATE DATABASE_DEFAULT +
' ON ' +
QUOTENAME(c.name) + '.' +
QUOTENAME(b.name) +
ISNULL(f.included_cols,'') +
CASE WHEN filter_definition IS NOT NULL THEN ' WHERE ' + filter_definition +
')' ELSE '' END +
' WITH (DROP_EXISTING = ON)' +
' ON ' +
QUOTENAME(ISNULL(@move_to,d.name)) AS create_script
FROM sys.indexes a (NOLOCK)
INNER JOIN sys.all_objects b (NOLOCK)on a.object_id = b.object_id
inner join sys.schemas c (NOLOCK)on c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d (NOLOCK)on d.data_space_id = a.data_space_id
LEFT JOIN (
SELECT DISTINCT a.object_id,index_id ,
'(' + STUFF((
SELECT ',' + QUOTENAME([name]) + CASE a.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END as [text()]
FROM sys.columns b (NOLOCK)
INNER JOIN sys.index_COLUMNS c on c.column_id = b.column_id and b.object_id = c.object_id
WHERE a.object_id = b.object_id and c.index_id = a.index_id
ORDER BY a.key_ordinal
FOR XML PATH ('')),1,1,'') + ')' index_cols
FROM sys.index_COLUMNS a (NOLOCK)
WHERE is_included_column = 0
) e on e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN (
SELECT DISTINCT a.object_id,index_id ,
' (' + STUFF((
SELECT ',' + QUOTENAME([name]) as [text()]
FROM sys.columns b (NOLOCK)
INNER JOIN sys.index_COLUMNS c on c.column_id = b.column_id and b.object_id = c.object_id
WHERE a.object_id = b.object_id and c.index_id = a.index_id
ORDER BY a.key_ordinal
FOR XML PATH ('')),1,1,'') + ')' included_cols
FROM sys.index_COLUMNS a (NOLOCK)
WHERE is_included_column = 1
) f on f.object_id = a.object_id AND f.index_id = a.index_id
INNER JOIN sys.partitions g (NOLOCK) on g.object_id = a.object_id and g.index_id = a.index_id
where a.type = 6 and
c.name != 'sys' and
b.name like '%'+@table +'%' and
c.name like '%'+@schema +'%'
declare @move_from VARCHAR(100) = 'DATA1' --filesgroup to move data off
declare @move_to VARCHAR(100) = 'DATA' --filesgroup to move data off
IF OBJECT_ID('tempdb..#result_set') IS NOT NULL DROP TABLE #result_set
CREATE TABLE #result_set(object_id bigint, index_id int, script VARCHAR(MAX))
INSERT INTO #result_set
exec sp_getIndexes @move_to = @move_to
select b.script,f.name lob_fg,g.name data_fg
from sys.indexes a
left join #result_set b on a.object_id = b.object_id and a.index_id = b.index_id
inner join sys.objects c on c.object_id = a.object_id
inner join sys.schemas d on d.schema_id = c.schema_id
inner join sys.tables e on e.object_id = b.object_id
left join sys.data_spaces f on f.data_space_id = e.lob_data_space_id
left join sys.data_spaces g on g.data_space_id = a.data_space_id
where (
d.name != 'sys' and
g.name = ISNULL(@move_from ,g.name) and
g.name != isnull(@move_to,g.name) and
1=1
)
OR
(f.name = @move_from and a.index_id>1)
UNION ALL
select 'CREATE CLUSTERED INDEX [x] on ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + '(' + QUOTENAME(column_name) + ') WITH(SORT_IN_TEMPDB=ON,DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@move_to) + '
DROP INDEX [x] ON ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name), lob_fg,data_fg
from (
select b.name table_name,c.name schema_name,d.name column_name,row_number() over (partition by d.object_id order by column_id) r
,f.name lob_fg, e.name data_fg
from sys.indexes a (NOLOCK)
inner join sys.tables b (NOLOCK) on a.object_id = b.object_id
inner join sys.schemas c (NOLOCK) on c.schema_id = b.schema_id
inner join sys.columns d (NOLOCK) on d.object_id = b.object_id
inner join sys.data_spaces e (NOLOCK) on e.data_space_id = a.data_space_id
left join sys.data_spaces f (NOLOCK) on f.data_space_id = b.lob_data_space_id
where index_id = 0 and e.name = @move_from and ISNULL(f.name,'') != @move_from) x
where r = 1
UNION ALL
SELECT 'CREATE PARTITION FUNCTION '+schema_name+'_' + table_name +'('+
CASE data_type
WHEN 'VARCHAR' THEN 'VARCHAR('+ CAST(max_length AS VARCHAR(10)) + ')'
WHEN 'NVARCHAR' THEN 'NVARCHAR('+ CAST(max_length/2 AS VARCHAR(10)) + ')'
WHEN 'NUMERIC' THEN 'NUMERIC('+ CAST(precision AS VARCHAR(10)) + ','+ CAST(scale AS VARCHAR(10))+')'
ELSE data_type
END
+') AS RANGE RIGHT FOR VALUES ('+
CASE data_type
WHEN 'int' THEN CAST(0 AS VARCHAR(1))
WHEN 'numeric' THEN CAST(0 AS VARCHAR(1))
WHEN 'BIT' THEN CAST(0 AS VARCHAR(1))
WHEN 'DATE' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'DATETIME' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'DATETIME2' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'VARCHAR' THEN ''''''
WHEN 'NVARCHAR' THEN ''''''
END
+')
CREATE PARTITION SCHEME '+schema_name+'_' + table_name +' AS PARTITION '+schema_name+'_' + table_name +' to ('+QUOTENAME(@move_to)+','+QUOTENAME(@move_to)+')
CREATE CLUSTERED INDEX '+schema_name+'_' + table_name +' ON '+QUOTENAME(schema_name)+'.' + QUOTENAME(table_name) + '(' +column_name +') with (sort_in_tempdb = on,DATA_COMPRESSION=PAGE) on ' +schema_name +'_' + table_name + '(' +column_name +')
CREATE CLUSTERED INDEX '+schema_name+'_' + table_name +' ON '+QUOTENAME(schema_name)+'.' + QUOTENAME(table_name) + '(' +column_name +') with (drop_existing = on,sort_in_tempdb = on,DATA_COMPRESSION=PAGE) on ' + QUOTENAME(@move_to) + '
DROP INDEX '+schema_name+'_' + table_name +' ON '+QUOTENAME(schema_name)+'.' + QUOTENAME(table_name) +'
DROP PARTITION SCHEME '+schema_name+'_' + table_name +'
DROP PARTITION FUNCTION '+schema_name+'_' + table_name ,lob_fg,data_fg
FROM (
select b.name table_name,c.name schema_name,d.name column_name,row_number() over (partition by d.object_id order by column_id) r
,f.name lob_fg, e.name data_fg,g.name data_type,d.max_length,d.precision,d.scale
from sys.indexes a (NOLOCK)
inner join sys.tables b (NOLOCK) on a.object_id = b.object_id
inner join sys.schemas c (NOLOCK) on c.schema_id = b.schema_id
inner join sys.columns d (NOLOCK) on d.object_id = b.object_id
inner join sys.data_spaces e (NOLOCK) on e.data_space_id = a.data_space_id
left join sys.data_spaces f (NOLOCK) on f.data_space_id = b.lob_data_space_id
inner join sys.types g (NOLOCK) ON g.system_type_id = d.system_type_id
where ISNULL(f.name,'') = @move_from
AND g.name != 'varchar' AND d.max_length !=-1
AND a.index_id = 0
) x
where r = 1
UNION ALL
SELECT 'CREATE PARTITION FUNCTION '+schema_name+'_' + table_name +'('+
CASE data_type
WHEN 'VARCHAR' THEN 'VARCHAR('+ CAST(max_length AS VARCHAR(10)) + ')'
WHEN 'NVARCHAR' THEN 'NVARCHAR('+ CAST(max_length/2 AS VARCHAR(10)) + ')'
WHEN 'NUMERIC' THEN 'NUMERIC('+ CAST(precision AS VARCHAR(10)) + ','+ CAST(scale AS VARCHAR(10))+')'
ELSE data_type
END
+') AS RANGE RIGHT FOR VALUES ('+
CASE data_type
WHEN 'int' THEN CAST(0 AS VARCHAR(1))
WHEN 'numeric' THEN CAST(0 AS VARCHAR(1))
WHEN 'BIT' THEN CAST(0 AS VARCHAR(1))
WHEN 'DATE' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'DATETIME' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'DATETIME2' THEN ''''+CAST('20300101' AS VARCHAR(10))+''''
WHEN 'VARCHAR' THEN ''''''
WHEN 'NVARCHAR' THEN ''''''
END
+')
CREATE PARTITION SCHEME '+schema_name+'_' + table_name +' AS PARTITION '+schema_name+'_' + table_name +' to ('+QUOTENAME(@move_to)+','+QUOTENAME(@move_to)+')
'+
replace(script,quotename(@move_to),quotename(schema_name+'_' + table_name) + '('+column_name+')' ) +'
'+
script +'
DROP PARTITION SCHEME '+schema_name+'_' + table_name +'
DROP PARTITION FUNCTION '+schema_name+'_' + table_name ,lob_fg,data_fg
FROM (
select b.name table_name,c.name schema_name,d.name column_name,row_number() over (partition by d.object_id order by column_id) r
,f.name lob_fg, e.name data_fg,g.name data_type,d.max_length,d.precision,d.scale
,script
from sys.indexes a (NOLOCK)
inner join sys.tables b (NOLOCK) on a.object_id = b.object_id
inner join sys.schemas c (NOLOCK) on c.schema_id = b.schema_id
inner join sys.columns d (NOLOCK) on d.object_id = b.object_id
inner join sys.data_spaces e (NOLOCK) on e.data_space_id = a.data_space_id
left join sys.data_spaces f (NOLOCK) on f.data_space_id = b.lob_data_space_id
inner join sys.types g (NOLOCK) ON g.system_type_id = d.system_type_id
LEFT JOIN #result_set h on h.object_id = a.object_id and h.index_id = a.index_id
where 1=1
AND ISNULL(f.name,'') = @move_from
AND NOT(g.name = 'varchar' AND d.max_length =-1)
AND a.index_id = 1
AND PATINDEX('%'+d.name+'%', h.script)>0
) x
where r = 1
SQL Notes 