Article
10/04/2012

How to: Inquire About the Full-Text Key Column (Transact-SQL)

Typically, the result of CONTAINSTABLE or FREETEXTTABLE rowset-valued functions need to be joined with the base table. In such cases, you need to know the unique key column name. You can inquire whether a given unique index is used as the full-text key, and you can obtain the identifier of the full-text key column.

To inquire whether a given unique index is used as the full-text key column

Use a SELECT statement to call the INDEXPROPERTY function. In the function call use the OBJECT_ID function to convert the name of the table (table_name) into the table ID, specify the name of a unique index for the table, and specify the IsFulltextKey index property, as follows:
```
SELECT INDEXPROPERTY( OBJECT_ID('table_name'), 'index_name',  'IsFulltextKey' );
```
This statement returns 1 if the index is used to enforce uniqueness of the full-text key column and 0 if it is not.

For more information, see Example A, later in this topic.

To find the identifier of the full-text key column

Each full-text enabled table has a column that is used to enforce unique rows for the table (the uniquekey column). The TableFulltextKeyColumn property, obtained from the OBJECTPROPERTYEX function, contains the column ID of the unique key column.

To obtain this identifier, you can use a SELECT statement to call the OBJECTPROPERTYEX function. Use the OBJECT_ID function to convert the name of the table (table_name) into the table ID and specify the TableFulltextKeyColumn property, as follows:
```
SELECT OBJECTPROPERTYEX(OBJECT_ID( 'table_name'), 'TableFulltextKeyColumn' ) AS 'Column Identifier';
```
For more information, see examples "B. Obtaining the identifier of the full-text key column"and "C. Obtaining the unique key column name," later in this topic.

Example

The following examples all use the Document table of the AdventureWorks2008R2 database.

A. Inquiring whether a given index is used as the full-text key column

The following example inquires whether the PK_Document_DocumentID index is used to enforce the uniqueness of the full-text key column, as follows:

USE AdventureWorks2008R2;
GO
SELECT INDEXPROPERTY ( OBJECT_ID('Production.Document'), 'PK_Document_DocumentID',  'IsFulltextKey' )

This example returns 1 if the PK_Document_DocumentID index is used to enforce uniqueness of the full-text key column. Otherwise, it returns 0 or NULL. NULL implies you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.

B. Obtaining the identifier of the full-text key column

The following example returns the identifier of the full-text key column or NULL. NULL implies that you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.

USE AdventureWorks2008R2;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn');
GO

C. Obtaining the unique key column name

The following example shows how to use the identifier of the unique key column to obtain its name programmatically.

USE AdventureWorks2008R2;
GO
DECLARE @key_column sysname;
SET @key_column = Col_Name(Object_Id('Production.Document'),
ObjectProperty(Object_id('Production.Document'),
'TableFulltextKeyColumn') 
);
SELECT @key_column AS 'Unique Key Column';
GO

This example returns a result set column named Unique Key Column, which displays a single row containing the name of the unique key column of the Document table, DocumentID. Note that if this query contained an invalid index name, the index name did not correspond to the table, the table did not exist, and so forth, it would return NULL.

Share via