SQL Server FTI:如何检查表状态? [英] SQL Server FTI: How to check table status?

问题描述

在SQL Server全文索引方案中，我想知道表是否在其中

In the SQL Server Full-Text Indexing scheme i want to know if a table is in

• start_chage_tracking模式
• update_index模式
• start_change_tracking和start_background_updateindex模式

• start_chage_tracking mode
• update_index mode
• start_change_tracking and start_background_updateindex modes

问题是我将表设置为后台更新索引"，然后告诉它开始更改跟踪"，但是几个月后，它似乎没有在跟踪更改.

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.

我如何查看后台更新索引"和更改跟踪"标志的状态?

How i can i see the status of "background updateindex" and "change tracking" flags?

示例:

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'.

很显然，表具有处于索引状态，我只想知道该表可以显示给用户(即我).

Obviously a table has an indexing status, i just want to know it show i can display it to the user (i.e. me).

另一个可用的API:

EXECUTE sp_help_fulltext_tables

仅返回目录中的表，不返回其状态.

only returns the tables that are in the catalog, it doesn't return their status.

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

我可以获得整个目录的 PopulateStatus :

And i can get the PopulateStatus of an entire catalog:

SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') AS PopulateStatus

返回目录的状态:

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

但不是用于表.

SQL Server 2000 SP4

SQL Server 2000 SP4

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)

无论有任何错误，我都希望创建UI以便能够轻松查看其状态.

Regardless of any bug, i want to create UI to easily be able to see its status.

推荐答案

基督.我有一个很好的格式化答案. IE崩溃时，我一直在滚动以点击保存.

Christ. i had a whole nicely formatted answer. i was scrolling to hit save when IE crashed.

简短版本:

对象属性

• TableFullTextPopulateStatus
• TableFullTextBackgroundUpdateIndexOn
• TableFullTextCatalogId
• TableFullTextChangeTrackingOn
• TableFullTextKeyColumn
• TableHasActiveFulltextIndex

TableFullTextBackgroundUpdateIndexOn 1 =真 0 =假

TableFullTextBackgroundUpdateIndexOn 1=TRUE 0=FALSE

TableFullTextPopulateStatus 0 =无人口 1 =人口总数 2 =增量人口

TableFullTextPopulateStatus 0=No population 1=Full population 2=Incremental population

完整示例:

SELECT
    --indicates whether full-text change-tracking is enabled on the table (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextChangeTrackingOn') AS TableFullTextChangeTrackingOn,

    --indicate the population status of a full-text table (0=No population, 1=Full Population, 2=Incremental Population)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextPopulateStatus') AS TableFullTextPopulateStatus,

    --indicates whether a table has full-text background update indexing (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextBackgroundUpdateIndexOn') AS TableFullTextBackgroundUpdateIndexOn,

    -- provides the full-text catalog ID in which the full-text index data for the table resides (0=table is not indexed)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextCatalogId') AS TableFullTextCatalogId,

    --provides the column ID of the full-text unique key column (0=table is not indexed)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextKeyColumn') AS TableFullTextKeyColumn,

    --indicates whether a table has an active full-text index (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableHasActiveFulltextIndex') AS TableHasActiveFulltextIndex

这篇关于SQL Server FTI:如何检查表状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！