Rebuild TFS Full Text Index
English
Portuguese
Just to remember myself in the future. This script rebuild the full text index in the TfsWorkItemTracking database of TFS.
Without this full text index, you're not able to run queries in the History or Description fields of the work items. use TFSWorkitemtracking
GO
declare @dbname as sysname
declare @tblName as sysname
set @dbname = 'TFSWorkitemtracking'
set @tblName = 'WorkItemLongTexts'
declare @LCID as int
set @LCID = convert(int, (SELECT COLLATIONPROPERTY(
convert(nvarchar,DATABASEPROPERTYEX(@dbname,'collation')), 'LCID')))
select @lcid
-- if exists drop the index
if OBJECTPROPERTY(OBJECT_ID(@tblName),N'TableFulltextCatalogId') <> 0
begin
exec(N'DROP FULLTEXT INDEX ON ' + @tblName)
end
-- Default to Neutral if Language resource not available for Full Text
if not exists(select * from sys.fulltext_languages where lcid = @LCID)
begin
set @LCID = 0
end
declare @lcidChar nvarchar(128)
set @lcidChar = cast(@LCID as nvarchar)
-- create the index
exec (N'CREATE FULLTEXT INDEX ON ' + @tblName + '(
Words --full-text index column name
LANGUAGE '+ @lcidChar + ' --LCID for the language
)
KEY INDEX UQ_WorkItemLongTexts_ChangedOrder --Unique index
WITH CHANGE_TRACKING OFF --Population type')
Só para eu não esquecer. Esse script server para reconstruir o full text index no banco de dados TfsWorkItemTracking do team foundation server.
Sem esse indíce as consultas que utilizam os campos History ou Description dos Work Items não vão funcionar. use TFSWorkitemtracking
GO
declare @dbname as sysname
declare @tblName as sysname
set @dbname = 'TFSWorkitemtracking'
set @tblName = 'WorkItemLongTexts'
declare @LCID as int
set @LCID = convert(int, (SELECT COLLATIONPROPERTY(
convert(nvarchar,DATABASEPROPERTYEX(@dbname,'collation')), 'LCID')))
select @lcid
-- if exists drop the index
if OBJECTPROPERTY(OBJECT_ID(@tblName),N'TableFulltextCatalogId') <> 0
begin
exec(N'DROP FULLTEXT INDEX ON ' + @tblName)
end
-- Default to Neutral if Language resource not available for Full Text
if not exists(select * from sys.fulltext_languages where lcid = @LCID)
begin
set @LCID = 0
end
declare @lcidChar nvarchar(128)
set @lcidChar = cast(@LCID as nvarchar)
-- create the index
exec (N'CREATE FULLTEXT INDEX ON ' + @tblName + '(
Words --full-text index column name
LANGUAGE '+ @lcidChar + ' --LCID for the language
)
KEY INDEX UQ_WorkItemLongTexts_ChangedOrder --Unique index
WITH CHANGE_TRACKING OFF --Population type')