 |
|
SQL Server Full Text Catalogs Stored Procedures
|
By: Edgewood Solutions | Read Comments | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More |
|
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
Last Update: 2/28/2007
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |