SQL Server Full Text Catalogs Stored Procedures


By:   |   Updated: 2007-02-28   |   Comments   |   Related: More > 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
EXEC sp_help_fulltext_catalogs_cursor @mycursor OUTPUT, 'ctgDocumentSummary'
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @mycursor
END
CLOSE @mycursor
DEALLOCATE @mycursor
GO

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
EXEC sp_help_fulltext_columns_cursor @mycursor OUTPUT
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @mycursor
END
CLOSE @mycursor
DEALLOCATE @mycursor
GO

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
EXEC sp_help_fulltext_tables_cursor @mycursor OUTPUT, 'ctgDocumentSummary'
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @mycursor
END
CLOSE @mycursor
DEALLOCATE @mycursor
GO

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



Last Updated: 2007-02-28


get scripts

next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools