Par défaut

Publié par

Publié le

22 janvier 2013

Publié sous

T-SQL

commentaires

Poster un commentaire

Scripter la (re-)création des indexes

Ayant besoin, pour un article qui devrait être publié incessamment sous peu, d’un script permettant de récupérer la définition des indexes d’une table, je me suis mis à faire quelques recherches sur le Net. Malheureusement au bout de 10 mn de recherche infructueuse (je n’ai trouvé aucun script complet), je me suis donc décidé à le faire moi-même.

Le but est de générer dynamiquement le code sql de (re-)création d’indexes d’une table. Je me restreins uniquement aux indexes que j’utilise à 99,99% en BI à savoir : les indexes clustered, nonclustered et nonclustered columnstore (je laisse donc de côté les fulltext, xml et spatial indexes)

Le script présenté permet de gérer les ordres de suppression et de création des indexes avec gestion des clés primaires, des  indexes uniques,  des colonnes incluses,  des filtres et des options (il me manque quand même les options MAXDOP, ONLINE, SORT_IN_TEMPDB et DATA_COMPRESSION; ces informations n’étant pas stockées dans les metadata …).

Je vous laisse consulter quelques références MSDN sur le sujet :

declare @sqlStmt nvarchar(max)
declare @tableName nvarchar(256) = '[dbo].[myTable]'

select @sqlStmt = (
(
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(@tableName)
for xml path(''), root('MyString'), type
).value('/MyString[1]','nvarchar(max)')
)

execute sp_executesql @sqlStmt

Voilà, vous n’avez plus qu’à adapter ce script en fonction de vos envies et surtout de vos besoins. Retrouvez le script SQL sur skydrive http://sdrv.ms/VlpB3A

N.B : pour les options manquantes, vous pouvez définir vous-même vos valeurs par défaut, soit par l’intermédiaire de variables supplémentaires dans ce script, soit en utilisant une table qui vous permettra de gérer un paramétrage spécifique pour chacune de vos tables à gérer.

Enjoy 😉

Laisser un commentaire