[SQL Server] Réorganiser tous les Index FullText de plusieurs bases
EXECUTE sp_MSforeachdb '
IF UPPER(''?'') LIKE (''MEKTABA%'')
BEGIN
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N''[tempdb]..[#fulltext_catalogs]''))
BEGIN
DROP TABLE #fulltext_catalogs
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N''[tempdb]..[#Requete]''))
BEGIN
DROP TABLE #Requete
END
CREATE TABLE #fulltext_catalogs
(
Num_Lig INT IDENTITY(1,1)
,nom_base NVARCHAR(250) NULL
,catalog_nameVARCHAR(1000) NULL
)
CREATE TABLE #Requete
(
Num_Lig INT IDENTITY(1,1)
,nom_base NVARCHAR(250) NULL
,catalog_nameVARCHAR(1000) NULL
,requete VARCHAR(MAX)
)
USE [?];
DECLARE @catalog_name VARCHAR(1000)
,@string_execute VARCHAR(MAX)
,@nom_base NVARCHAR(250)
,@Nbre_Lig INT
,@Num_Lig INT
INSERT INTO #fulltext_catalogs (nom_base,catalog_name)
SELECT DB_NAME() AS nom_base,[name] FROM sys.fulltext_catalogs
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #fulltext_catalogs
WHILE @Num_Lig<=@Nbre_Lig>
BEGIN
SELECT @catalog_name=catalog_name,@nom_base=nom_base FROM #fulltext_catalogs WHERE Num_Lig=@Num_Lig>
SELECT @string_execute = ''ALTER FULLTEXT CATALOG '' + @catalog_name + '' reorganize''
INSERT INTO #Requete (nom_base,catalog_name ,requete)
VALUES (@nom_base,@catalog_name,@string_execute)
EXEC (@string_execute)
SELECT @Num_Lig = @Num_Lig + 1
END
SELECT Num_Lig,nom_base,catalog_name,requete FROM #Requete ORDER BY Num_Lig
DROP TABLE #fulltext_catalogs
DROP TABLE #Requete
END'