declare @db_name sysname;
declare db cursor for
select sd.name as db_name from sys.dm_fts_active_catalogs fts inner join sys.databases sd on fts.database_id = sd.database_id ORDER BY sd.name
open db
fetch next from db into @db_name
while @@fetch_status = 0
begin
declare @fts_name sysname;
declare fts cursor for
select fts.name as fts_name from sys.dm_fts_active_catalogs fts inner join sys.databases sd on fts.database_id = sd.database_id WHERE sd.name = @db_name
open fts
fetch next from fts into @fts_name
while @@fetch_status = 0
begin
print 'USE ' + QUOTENAME(@db_name) + '; ALTER FULLTEXT CATALOG ' + QUOTENAME(@fts_name) + ' REBUILD;'
fetch next from fts into @fts_name
end
close fts;
deallocate fts;
fetch next from db into @db_name
end
close db;
deallocate db;
