Get Fulltext Index Status

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 6 Jun 2020

Description

Returns the status of all FTIs in a database

Code

USE MyDatabase

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

DECLARE

@tbl SYSNAME

,@cat SYSNAME

CREATE TABLE #temp_ca

(

TABLE_OWNER VARCHAR(100)

,TABLE_NAME VARCHAR(256)

,FULLTEXT_KEY_INDEX_NAME VARCHAR(256)

,FULLTEXT_KEY_COLID INT

,FULLTEXT_INDEX_ACTIVE INT

,FULLTEXT_CATALOG_NAME VARCHAR(256)

)

CREATE TABLE #temp_status

(

Catalog VARCHAR(64)

,TblName VARCHAR(64)

,IsEnabled bit

,ChangeTracking VARCHAR(24)

,PopulateStatus VARCHAR(64)

,RowCnt INT

,FTS_CT INT

,Delta INT

,PercentCompleted VARCHAR(128)

,path NVARCHAR(260)

)

INSERT INTO #temp_ca

EXEC sp_help_fulltext_tables

DECLARE ca_cursor CURSOR FOR

SELECT TABLE_NAME, FULLTEXT_CATALOG_NAME FROM #temp_ca

OPEN ca_cursor

FETCH NEXT FROM ca_cursor INTO @tbl, @cat

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #temp_status

SELECT

CAST (@cat as VARCHAR(40)) Catalog

, CAST(OBJECT_NAME(si.id) AS VARCHAR(25)) TblName

, CAST(OBJECTPROPERTY(tbl.id,'TableHasActiveFulltextIndex') AS BIT) AS [IsEnabled]

, CASE ISNULL(OBJECTPROPERTY(tbl.id,'TableFullTextBackgroundUpdateIndexon'),0)

+ ISNULL(OBJECTPROPERTY(tbl.id,'TableFullTextChangeTrackingon'),0)

WHEN 0 THEN 'Do not track changes'

WHEN 1 THEN 'Manual'

WHEN 2 THEN 'Automatic'

END [ChangeTracking]

, CASE FULLTEXTCATALOGPROPERTY ( @cat , 'PopulateStatus' )

WHEN 0 THEN 'Idle'

WHEN 1 THEN 'Full population in progress'

WHEN 2 THEN 'Paused'

WHEN 3 THEN 'Throttled'

WHEN 4 THEN 'Recovering'

WHEN 5 THEN 'Shutdown'

WHEN 6 THEN 'Incremental population in progress'

WHEN 7 THEN 'Building index'

WHEN 8 THEN 'Disk is full. Paused.'

WHEN 9 THEN 'Change tracking'

END PopulateStatus

, si.RowCnt, FULLTEXTCATALOGPROPERTY(@cat, 'ItemCount') FTS_CT

, si.RowCnt - FULLTEXTCATALOGPROPERTY(@cat, 'ItemCount') Delta

, CAST ( 100.0 * FULLTEXTCATALOGPROPERTY(@cat, 'ItemCount')

/ CAST(si.RowCnt AS DECIMAL (14,2))

AS VARCHAR) +'%' AS PercentCompleted

, ISNULL(cat.path, 'Check Default Path')

FROM dbo.sysobjects AS tbl

INNER JOIN sysusers AS stbl

ON stbl.uid = tbl.uid

INNER JOIN sysfulltextcatalogs as cat

ON (cat.ftcatid=OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId'))

AND (1=CasT(OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId') AS BIT))

INNER JOIN sysindexes AS si

ON si.id = tbl.id

WHERE si.indid in (0,1) and si.id = object_id(@tbl)

FETCH NEXT FROM ca_cursor INTO @tbl, @cat

END

CLOSE ca_cursor

DEALLOCATE ca_cursor

SELECT * FROM #temp_status

DROP TABLE #temp_ca

DROP TABLE #temp_status