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 MyDatabaseSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ONDECLARE @tbl SYSNAME,@cat SYSNAMECREATE 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_caEXEC sp_help_fulltext_tablesDECLARE ca_cursor CURSOR FORSELECT TABLE_NAME, FULLTEXT_CATALOG_NAME FROM #temp_caOPEN ca_cursorFETCH NEXT FROM ca_cursor INTO @tbl, @catWHILE @@FETCH_STATUS = 0BEGININSERT INTO #temp_statusSELECTCAST (@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 tblINNER JOIN sysusers AS stblON stbl.uid = tbl.uidINNER JOIN sysfulltextcatalogs as catON (cat.ftcatid=OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId'))AND (1=CasT(OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId') AS BIT))INNER JOIN sysindexes AS siON si.id = tbl.idWHERE si.indid in (0,1) and si.id = object_id(@tbl)FETCH NEXT FROM ca_cursor INTO @tbl, @catENDCLOSE ca_cursorDEALLOCATE ca_cursorSELECT * FROM #temp_statusDROP TABLE #temp_caDROP TABLE #temp_status