Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

FULLTEXTCATALOGPROPERTY (Transact-SQL)

Returns information about full-text catalog properties.

Topic link icon Transact-SQL Syntax Conventions


FULLTEXTCATALOGPROPERTY ('catalog_name' ,'property')
NoteNote

The following properties will be removed in a future release of SQL Server: LogSize and PopulateStatus. Avoid using these properties in new development work, and plan to modify applications that currently use any of them.

catalog_name

Is an expression containing the name of the full-text catalog.

property

Is an expression containing the name of the full-text catalog property. The table lists the properties and provides descriptions of the information returned.

Property

Description

AccentSensitivity

Accent-sensitivity setting.

0 = Accent insensitive

1 = Accent sensitive

IndexSize

Logical size of the full-text catalog in megabytes (MB).

For more information, see "Remarks," later in this topic.

ItemCount

Number of full-text indexed items currently in the full-text catalog.

LogSize

Supported for backward compatibility only. Always returns 0.

Size, in bytes, of the combined set of error logs associated with a Microsoft Search Service full-text catalog.

MergeStatus

Whether a master merge is in progress.

0 = master merge not in progress

1 = master merge in progress

PopulateCompletionAge

The difference in seconds between the completion of the last full-text index population and 01/01/1990 00:00:00.

Only updated for full and incremental crawls. Returns 0 if no population has occurred.

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

UniqueKeyCount

Number of unique keys in the full-text catalog.

ImportStatus

Whether the full-text catalog is being imported.

0 = The full-text catalog is not being imported.

1 = The full-text catalog is being imported.

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2008, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as FULLTEXTCATALOGPROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

FULLTEXTCATALOGPROPERTY ('catalog_name' ,'IndexSize') looks at only fragments with status 4 or 6 as shown in sys.fulltext_index_fragments. These fragments are part of the logical index. Therefore, the IndexSize property returns only the logical index size. During an index merge, however, the actual index size might be double its logical size. To find the actual size that is being consumed by a full-text index during a merge, use the sp_spaceused system stored procedure. That procedure looks at all fragments associated with a full-text index.

It is important that applications do not wait in a tight loop, checking for the PopulateStatus property to become idle (indicating that population has completed) because this takes CPU cycles away from the database and full-text search processes, and causes timeouts. In addition, it is usually a better option to check the corresponding PopulateStatus property at the table level, TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function. This and other new full-text properties in OBJECTPROPERTYEX provide more granular information about full-text indexing tables. For more information, see OBJECTPROPERTYEX (Transact-SQL).

The following example returns the number of full-text indexed items in a full-text catalog named Cat_Desc.

USE AdventureWorks2008R2;
GO
SELECT fulltextcatalogproperty('Cat_Desc', 'ItemCount');
GO
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.