Automations

Rebuid All FullText Indexes On Every Database

Use this script to rebuild all FullText Indexes across every database. It would be wise to set this up as a repeated Job on a maintenance window. Remember; Standard Editions does not allow indexes to be rebuilt (online=on) so FTI’s will be unavailable during the course of the rebuild. If you are using Enterprise Edition you could always incorporate with (online=on) as this is supported for Enterprise.

use master;
set nocount on

declare @rebuild_all_fti varchar(max)
set @rebuild_all_fti = ''
select @rebuild_all_fti = @rebuild_all_fti
+
'use [' + name + ']; '
+ char(10) +
'declare @rebuild_fti_'
+ cast(database_id as varchar(200)) + ' varchar(255)' + char(10) +
'set @rebuild_fti_'
+ cast(database_id as varchar(200)) + ' = '''''
+ char(10) +
'select @rebuild_fti_'
+ cast(database_id as varchar(200)) + ' = @rebuild_fti_' + cast(database_id as varchar(200)) + ' + '''
+ char(10) +
'alter fulltext catalog '' + sftc.name + '' rebuild; ''
+ char(10) + char(10)
from sys.fulltext_catalogs as sftc order by [name] asc'
+ char(10) +
'exec (@rebuild_fti_' + cast(database_id as varchar(200)) + ')' + char(10) + char(10)
from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb') order by name asc

exec (@rebuild_all_fti) --for xml path(''), type

Hope this is useful.

Advertisements

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s