[SQL Server] Recréer tous les index d'une BD avec modification du fichier de données
USE MaBase
go
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Liste_Tables]'))
BEGIN
DROP TABLE #Liste_Tables
END
CREATE TABLE #Liste_Tables
(
Num_Table INT IDENTITY(1,1),
Nom_Table NVARCHAR(150)
)
INSERT INTO #Liste_Tables(Nom_Table)
SELECT name Nom_Table FROM sys.objects WHERE type_desc='USER_TABLE'
ORDER BY name
DECLARE @Requete NVARCHAR(MAX)
,@NomTable NVARCHAR(256)
,@Nbre_Tables INT
,@Num_Table INT
,@Group_Ficher_Source VARCHAR(35)
,@Group_Ficher_Destination VARCHAR(35)
SELECT @Group_Ficher_Source ='PRIMARY'
,@Group_Ficher_Destination ='MaBase_INDEX'
SELECT @Nbre_Tables=COUNT(*) FROM #Liste_Tables
SELECT @Num_Table = 1
WHILE @Num_Table <= @Nbre_Tables
BEGIN
SELECT @NomTable = Nom_Table FROM #Liste_Tables WHERE Num_Table=@Num_Table
SELECT @Requete =(
(
SELECT
'IF EXISTS (SELECT 1 from sys.indexes where object_id=' + cast(indexes.object_id as nvarchar) + ' and index_id=' + cast(indexes.index_id as nvarchar)+ ')' + char(10)
+ CASE
WHEN indexes.is_primary_key = 1 then
' ALTER TABLE [' + schemas.name + '].[' + objects.name + '] DROP CONSTRAINT ' + indexes.name
+ ';' + char(10)
+ 'ALTER TABLE [' + schemas.name + '].[' + objects.name + '] ADD CONSTRAINT [' + indexes.name + '] PRIMARY KEY '
+ CASE WHEN INDEXPROPERTY(indexes.object_id,indexes.name,'ISCLUSTERED') = 1 then ' CLUSTERED ' ELSE ' NONCLUSTERED ' END
+ '(' + key_cols.list + ')'
ELSE
'DROP INDEX [' + indexes.name + '] ON [' + schemas.name + '].[' + objects.name + ']'
+ ';' + char(10)
+ 'CREATE '
+ CASE WHEN indexes.is_unique= 1 then ' UNIQUE ' ELSE '' END
+ CASE indexes.type WHEN 1 then ' CLUSTERED ' WHEN 2 then ' NONCLUSTERED ' WHEN 6 then ' NONCLUSTERED COLUMNSTORE ' END
+ ' INDEX [' + indexes.name + '] '
+' ON [' + schemas.name + '].[' + objects.name + '] '
+ CASE WHEN indexes.type in(1,2) then '(' + key_cols.list + ')' ELSE '' END
+ CASE
WHEN included_cols.list IS NULL then ''
WHEN indexes.type in(1,2) then ' INCLUDE (' + included_cols.list + ')'
WHEN indexes.type = 6 then '(' + included_cols.list + ')'
ELSE ''
END
+ CASE WHEN indexes.has_filter=1 then ' WHERE ' + indexes.filter_definition ELSE '' END
END
+ CASE
WHEN indexes.type in(1,2) then
' WITH ('
+ ' PAD_INDEX = ' + CASE WHEN indexes.is_padded=0 then ' OFF' ELSE ' ON' END
+ ', STATISTICS_NORECOMPUTE = ' + CASE WHEN INDEXPROPERTY(indexes.object_id,indexes.name,'IsAutoStatistics') = 0 then ' OFF' ELSE ' ON' END
+ ', IGNORE_DUP_KEY = ' + CASE WHEN indexes.ignore_dup_key=0 then ' OFF' ELSE ' ON' END
+ ', ALLOW_ROW_LOCKS = ' + CASE WHEN indexes.allow_row_locks=0 then ' OFF' ELSE ' ON' END
+ ', ALLOW_PAGE_LOCKS = ' + CASE WHEN indexes.allow_page_locks=0 then ' OFF' ELSE ' ON' END
+ CASE WHEN indexes.fill_factor = 0 then '' ELSE ' , FILLFACTOR = ' + cast(indexes.fill_factor as nvarchar(10)) END
+ ')'
ELSE
''
END
+ ' ON [' + data_spaces.name + ']' + CASE WHEN data_spaces.type ='PS' then '(' + partition_cols.list + ')' ELSE '' END
+ ';' + char(10)
+ CASE
WHEN indexes.is_disabled = 1 then 'ALTER INDEX [' + indexes.name + '] ON [' + schemas.name + '].[' + objects.name + '] DISABLE' + ';' + char(10)
ELSE ''
END as 'data()'
from sys.indexes
inner join sys.objects on objects.object_id = indexes.object_id
inner join sys.schemas on schemas.schema_id = objects.schema_id
inner join sys.data_spaces on data_spaces.data_space_id = indexes.data_space_id
cross apply
(
SELECT
STUFF(
(
SELECT ',[' + syscolumns.name + ']' + CASE WHEN index_columns.is_descENDing_key=1 then ' desc' ELSE ' asc' END as 'data()'
from sysindexkeys
INNER JOIN syscolumns ON SYSCOLUMNS.id = sysindexkeys.id AND SYSCOLUMNS.colid = sysindexkeys.colid
inner join sys.index_columns on index_columns.object_id = sysindexkeys.ID and index_columns.index_id = sysindexkeys.indid and index_columns.column_id = syscolumns.colid
where sysindexkeys.id=indexes.object_id
and sysindexkeys.indid=indexes.index_id
and index_columns.key_ordinal > 0
order by index_columns.key_ordinal
for xml path('')
),1,1,'')
) key_cols(list)
cross apply
(
SELECT
STUFF(
(
SELECT ',[' + syscolumns.name + ']' as 'data()'
from sysindexkeys
INNER JOIN syscolumns ON SYSCOLUMNS.id = sysindexkeys.id AND SYSCOLUMNS.colid = sysindexkeys.colid
inner join sys.index_columns on index_columns.object_id = sysindexkeys.ID and index_columns.index_id = sysindexkeys.indid and index_columns.column_id = syscolumns.colid
where sysindexkeys.id=indexes.object_id
and sysindexkeys.indid=indexes.index_id
and index_columns.is_included_column = 1
order by syscolumns.colid
for xml path('')
),1,1,'')
) included_cols(list)
cross apply
(
SELECT
STUFF(
(
SELECT ',[' + syscolumns.name + ']' as 'data()'
from sysindexkeys
INNER JOIN syscolumns ON SYSCOLUMNS.id = sysindexkeys.id AND SYSCOLUMNS.colid = sysindexkeys.colid
inner join sys.index_columns on index_columns.object_id = sysindexkeys.ID and index_columns.index_id = sysindexkeys.indid and index_columns.column_id = syscolumns.colid
where sysindexkeys.id=indexes.object_id
and sysindexkeys.indid=indexes.index_id
and index_columns.partition_ordinal > 0
order by index_columns.partition_ordinal
for xml path('')
),1,1,'')
) partition_cols(list)
where indexes.type in(1,2,6)
and indexes.is_hypothetical = 0
and objects.type='U'
and indexes.object_id=object_id(@NomTable)
and INDEXPROPERTY(indexes.object_id,indexes.name,'ISCLUSTERED') <> 1
and indexes.is_primary_key <> 1
and indexes.is_unique<> 1
for xml path(''), root('MyString'), type
).value('/MyString[1]','nvarchar(max)')
)
SELECT @Requete=REPLACE(@Requete,@Group_Ficher_Source,@Group_Ficher_Destination)
EXECUTE sp_executesql@Requete
SELECT @Num_Table = @Num_Table + 1
END