Three Undocumented SQL Server 2014 Full-Text Search Stored Procedures
SQL Server 2014 supports the following three undocumented full-text search system stored procedures:
- sp_fulltext_recycle_crawl_log
- sp_MShelpfulltextindex
- sp_MShelpfulltextscript
sp_fulltext_recycle_crawl_log
The sp_fulltext_recycle_crawl_log system stored procedure is used to recycle crawl log for the
given full-text catalog name.
Syntax
sp_fulltext_recycle_crawl_log [ @ftcat = ] ‘ftcat’
Arguments
[ @ftcat = ] ‘ftcat’
Is the full-text catalog name to recycle crawl log. @ftcat is sysname (nvarchar(128)).
@ftcat parameter allows spaces in the name, but cannot be a 0-length string.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None
Remarks
The sp_fulltext_catalog cannot be executed under master, tempdb or model databases.
Permissions
Only the members of the dbowner fixed database role can execute the sp_fulltext_recycle_crawl_log
system stored procedure to recycle crawl log.
Example
This is the example to recycle crawl log for the SalesFTCatalog full-text catalog:
EXEC sp_fulltext_recycle_crawl_log @ftcat = ‘SalesFTCatalog’
sp_MShelpfulltextindex
The sp_MShelpfulltextindex system stored procedure is used to return the full-text index name
for the given full-text table name.
Syntax
sp_MShelpfulltextindex [ @tablename = ] ‘tablename’
Arguments
[ @tablename = ] ‘tablename’
Is the full-text table name to return the full-text index. @tablename is nvarchar(517).
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| ind_name | nvarchar(128) | Is the full-text index name |
Remarks
Each index can have up to 16 associated keys; all of them need to be non-nullalbe for the index
to be qualified as a full-text index.
Note. Only one full-text index is allowed per table.
Permissions
Execute permissions default to members of the public role.
Example
This is the example to return the full-text index name for the SalesFTable full-text table:
EXEC sp_MShelpfulltextindex @tablename = ‘SalesFTable’
sp_MShelpfulltextscript
The sp_MShelpfulltextscript system stored procedure is used to return the full-text index name,
full-text catalog name and active/passive status of the full-text index for the given full-text
table name.
Syntax
sp_MShelpfulltextscript [ @tablename = ] ‘tablename’
Arguments
[ @tablename = ] ‘tablename’
Is the full-text table name to return the full-text index information.
@tablename is nvarchar(517).
Return Code Values
None (success) or 1 (failure).
Result Sets
None
Remarks
Each index can have up to 16 associated keys; all of them need to be non-nullalbe for the index
to be qualified as a full-text index.
Note. Only one full-text index is allowed per table.
Permissions
Execute permissions default to members of the public role.
Example
This is the example to return the full-text index name, full-text catalog name and active/passive
status of the full-text index for the SalesFTable full-text table:
EXEC sp_MShelpfulltextscript @tablename = ‘SalesFTable’