title: SQL Server FTI: How to check table status?
body: <p>In the SQL Server Full-Text Indexing scheme i want to know if a table is in</p>
  
  <ul>
  <li><code>start_chage_tracking</code> mode</li>
  <li><code>update_index</code> mode</li>
  <li><code>start_change_tracking</code> and <code>start_background_updateindex</code> modes</li>
  </ul>
  
  <p>The problem is that i set my tables to "background update index", and then tell it to "start change tracking", but then some months later it doesn't seem to be tracking changes.</p>
  
  <p>How i can i see the status of "background updateindex" and "change tracking" flags?</p>
  
  <p><strong>example:</strong></p>
  
  <pre><code>sp_fulltext_table @tabname='DiaryEntry', @action='start_background_updateindex' 
  
  Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
  Full-text auto propagation is currently enabled for table 'DiaryEntry'.
  
  sp_fulltext_table @tabname='Ticket', @action='start_background_updateindex' 
  Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
  Full-text auto propagation is currently enabled for table 'Ticket'.
  </code></pre>
  
  <p>Obviously a table <em>has</em> an indexing status, i just want to know it show i can display it to the user (i.e. me).</p>
  
  <p>The other available API:</p>
  
  <pre><code>EXECUTE sp_help_fulltext_tables
  </code></pre>
  
  <p>only returns the tables that are in the catalog, it doesn't return their status.</p>
  
  <pre><code>TABLE_OWNER  TABLE_NAME  FULLTEXT_KEY_INDEX_NAME  FULLTEXT_KEY_COLID  FULLTEXT_INDEX_ACTIVE  FULLTEXT_CATALOG_NAME
  ===========  ==========  =======================  ==================  =====================   =====================
  dbo          DiaryEntry  PK_DiaryEntry_GUID       1                   1                      FrontlineFTCatalog
  dbo          Ticket      PK__TICKET_TicketGUID    1                   1                      FrontlineFTCatalog
  </code></pre>
  
  <p>And i can get the <strong>PopulateStatus</strong> of an entire catalog:</p>
  
  <pre><code>SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') AS PopulateStatus
  </code></pre>
  
  <p>which returns a status for the catalog: </p>
  
  <pre><code>0 = Idle 
  1 = Full population in progress
  2 = Paused 
  3 = Throttled 
  4 = Recovering 
  5 = Shutdown 
  6 = Incremental population in progress 
  7 = Building index 
  8 = Disk is full. Paused.
  9 = Change tracking
  </code></pre>
  
  <p>but not for a table.</p>
  
  <hr>
  
  <p>SQL Server 2000 SP4</p>
  
  <pre><code>SELECT @@version
  Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
      Aug  6 2000 00:57:48 
      Copyright (c) 1988-2000 Microsoft Corporation
      Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
  </code></pre>
  
  <p>Regardless of any bug, i want to create UI to easily be able to see its status.</p>