Regénérer le DDL des indexes FULL TEXT

Comme il n’y a pas d’outil pour le faire et que j’ai dû le faire pour un client récemment (ne fonctionne qu’à partir de SQL Server 2008+):

use mabase ;

create table #ftsDDL (catalogname sysname, objname sysname, objtype varchar(20),
colname sysname, type_column_id sysname NULL, langid int, indexname sysname,
changetracking varchar(10), stoplistname sysname)

insert into #ftsDDL
select FTC.name 'Catalog Name', T.name 'Object name', 'Table' 'Object Type',
C.name 'colname', FTIC.type_column_id, FTIC.language_id, I.name 'UQIndexName',
FTI.change_tracking_state_desc,
case when STP.name <> '0' then STP.name else 'system' end
from sys.fulltext_indexes FTI
inner join sys.fulltext_catalogs FTC on FTC.fulltext_catalog_id = FTI.fulltext_catalog_id
inner join sys.tables T on FTI.object_id = T.object_id
inner join sys.indexes I on I.object_id = T.object_id
inner join sys.fulltext_index_columns FTIC on FTIC.object_id = FTI.object_id
inner join sys.columns C on C.column_id = FTIC.column_id and C.object_id = FTI.object_id
left outer join sys.fulltext_stoplists STP on STP.stoplist_id = FTI.stoplist_id
where I.is_unique = 1 and I.type = 1
and FTIC.type_column_id is NULL

	 UNION 

select FTC.name 'Catalog Name', V.name 'Object name', 'Indexed View' 'Object Type',
C.name 'colname', FTIC.type_column_id, FTIC.language_id, I.name 'UQIndexName',
FTI.change_tracking_state_desc,
case when STP.name <> '0' then STP.name else 'system' end
from sys.fulltext_indexes FTI
inner join sys.fulltext_catalogs FTC on FTC.fulltext_catalog_id = FTI.fulltext_catalog_id
inner join sys.views V on FTI.object_id = V.object_id
inner join sys.indexes I on I.object_id = V.object_id
inner join sys.fulltext_index_columns FTIC on FTIC.object_id = FTI.object_id
inner join sys.columns C on C.column_id = FTIC.column_id and C.object_id = FTI.object_id
left outer join sys.fulltext_stoplists STP on STP.stoplist_id = FTI.stoplist_id
where I.is_unique = 1 and I.type = 1
and FTIC.type_column_id is NULL
order by 1;

with cte as
 (select p1.catalogname, p1.objname, (select colname
 +case
when p2.type_column_id is NULL then '' else ' TYPE COLUMN *put type column here*' end+
' language '+cast(p2.langid as char(5))+','
from #ftsDDL p2 where p1.objname = p2.objname FOR XML PATH ('') ) as 'Columns',
p1.indexname, p1.changetracking, p1.stoplistname from #ftsDDL p1 group by catalogname,
objname, indexname, changetracking, stoplistname
)

select 'CREATE FULLTEXT INDEX ON '+objname+'('+substring(Columns,1,len(Columns)-1)+
') KEY INDEX ['+indexname+'] on ['+catalogname+'] WITH CHANGE_TRACKING '+changetracking+
', STOPLIST = ['+stoplistname+']' from cte;

drop table #ftsDDL;

/*

CREATE FULLTEXT INDEX ON V_ContentItmListGermany(abstr language 1031 ,desc language 1031 ,
subt language 1031 ,title language 1031 ) KEY INDEX [UI_V_ContentItmsListGermany]
on [Catalog_1] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttGermany(blob language 1031 ) KEY INDEX [UI_V_GenAtttGermany]
on [Catalog_1] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAttachmentDe(blob TYPE COLUMN *put type column here*
language 1031 ) KEY INDEX  [UI_V_GenAttachmentDe] on [Catalog_LanDe]
WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttUS(blob language 1033 ) KEY INDEX [UI_V_GenAtttUS]
on [Catalog_2] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAttachmentEn(blob TYPE COLUMN *put type column here*
language 1033 ) KEY INDEX  [UI_V_GenAttachmentEn] on [Catalog_LanEn]
WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttFrance(blob language 1036 ) KEY INDEX [UI_V_GenAtttFrance]
on [Catalog_3] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

*/

Les indexes FTS, contraitement aux catalogues ne peuvent pas être scriptés directement dans SSMS. Evidemment il faudra recréer les catalogues avant. Attention, si des colonnes indexées sont de type varbinary(max) ou image, il faudra préciser le nom de la colonne qui stocke l’extension (cf http://msdn.microsoft.com/en-us/library/ms187317.aspx) à la place de *put type column here*
(je ne peux pas les deviner)

Petite note intéressante, dans le cas de ce client, les indexes FTS étaient créés sur des vues indexées, d’où l’UNION entre les indexes liés à sys.tables et ceux liés à sys.views dans la table temporaire. Et pour transposer et concaténer les colonnes de l’index sur une ligne on utilise la technique avec FOR XML PATH(”) dans la CTE.

A+

ContentItmListGermany

Continuez votre lecture sur le blog :

• Repeupler un index FullText (Benjamin VESAN) [SQL Server]
• Retrouver les tables dont les stats ne sont plus compilées en automatique (David Baffaleuf) [SQL Server]
• Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]
• Vers un DIRECT_PATH_READ sur SQL Server ? (David Baffaleuf) [SQL Server]
• Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]