By: MSSQLTips | Comments | Related: > Full Text Search
Problem
As mentioned in a previous tip, there are a number of locations where full-text search information can be found in SQL Server 2000 and SQL Server 2005. SQL Server 2005 includes some dynamic management views, but in SQL Server 2000 there are no such views, requiring creation of your own statements or views to gather the information.
Solution
There are stored procedures in each database that can assist you in gathering information you need to document or work with full-text catalogs already present in your environment. As with other stored procedures that provide information on objects in SQL Server, these stored procedures start with "sp_help". The following table shows those stored procedures, their purpose, and information provided by the stored procedure:
| sp_help_fulltext_catalogs Must be run in the database in which it resides | |
| USE adventureworks2000 GO exec sp_help_fulltext_catalogs @fulltext_catalog_name = 'ctgDocumentSummary' | |
| ftcatid | The ID of the full-text catalog |
| Name | The name of the full-text catalog |
| Path | The physical location of the Gatherer Project folder |
| Status | The current status of the catalog: 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 |
| Number_FullText_Tables | The number of tables associated with the catalog |
| sp_help_fulltext_catalogs_cursor Must be run in the database in which it resides | |
| USE adventureworks2000 GO DECLARE @mycursor CURSOR | |
| ftcatid | The ID of the full-text catalog |
| Name | The name of the full-text catalog |
| Path | The physical location of the Gatherer Project folder |
| Status | The current status of the catalog: 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 |
| Number_FullText_Tables | The number of tables associated with the catalog |
| sp_help_fulltext_columns Must be run in the database in which it resides | |
| USE adventureworks2000 GO exec sp_help_fulltext_columns @table_name = 'Document' (optional: @column_name = 'column_name')
| |
| TABLE_OWNER | The owner of the table |
| TABLE_ID | ID of the table |
| TABLE_NAME | The name of the table |
| FULLTEXT_COLID | The ID of the column (from the table itself) |
| FULLTEXT_COLUMN_NAME | Column of the table that specifies the document type (applicable only when data type is image) |
| FULLTEXT_BLOBTP_COLNAME | The name of the column specifying the document type |
| FULLTEXT_BLOBTP_COLID | The ID of the document type column (from the table itself) |
| FULLTEXT_LANGUAGE | The language used for the full-text search of the column (expressed as a local identifier) |
| sp_help_fulltext_columns_cursor Must be run in the database in which it resides | |
| USE adventureworks2000 GO DECLARE @mycursor CURSOR | |
| TABLE_OWNER | The owner of the table |
| TABLE_ID | ID of the table |
| TABLE_NAME | The name of the table |
| FULLTEXT_COLID | The ID of the column (from the table itself) |
| FULLTEXT_COLUMN_NAME | Column of the table that specifies the document type (applicable only when data type is image) |
| FULLTEXT_BLOBTP_COLNAME | The name of the column specifying the document type |
| FULLTEXT_BLOBTP_COLID | The ID of the document type column (from the table itself) |
| FULLTEXT_LANGUAGE | The language used for the full-text search of the column (expressed as a local identifier) |
| sp_help_fulltext_tables Must be run in the database in which it resides | |
| USE adventureworks2000 GO exec sp_help_fulltext_tables @fulltext_catalog_name = 'ctgDocumentSummary' | |
| TABLE_OWNER | The owner of the table |
| TABLE_NAME | The name of the table associated with the full-text catalog |
| FULLTEXT_KEY_INDEX_NAME | The name of the unique index in the table |
| FULLTEXT_KEY_COLID | The ID of the column associated with the unique index |
| FULLTEXT_INDEX_ACTIVE | Specifies whether the columns associated with the full-text index are eligible for queries |
| FULLTEXT_CATALOG_NAME | The name of the full-text catalog |
| sp_help_fulltext_tables_cursor Must be run in the database in which it resides | |
| USE AdventureWorks2000 GO DECLARE @mycursor CURSOR | |
| TABLE_OWNER | The owner of the table |
| TABLE_NAME | The name of the table associated with the full-text catalog |
| FULLTEXT_KEY_INDEX_NAME | The name of the unique index in the table |
| FULLTEXT_KEY_COLID | The ID of the column associated with the unique index |
| FULLTEXT_INDEX_ACTIVE | Specifies whether the columns associated with the full-text index are eligible for queries |
| FULLTEXT_CATALOG_NAME | The name of the full-text catalog |
Next Steps
- These stored procedures are also available in SQL Server 2005 with the same syntax
- Use these stored procedures, as well as other Configuration Information to document and troubleshoot your full-text indexes and catalogs
- Get more recommendations for providing Full-Text Search from Microsoft
- Review information on sp_help_fulltext_tables, sp_help_fulltext_columns, and sp_help_fulltext_catalogs, as well as general information on Full-Text Searches on MSDN
- Read more Full-Text Search tips on MSSQLTIPS.com
About the author
MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
