Cool Tips‎ > ‎Indexes‎ > ‎

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

Comments