TSQL: How to check if column is fulltext enabled?

Question

I need to modify a column definition, but I would like to check if the column is full text enabled first. Is there a way to do such a check in a TSQL script?

I'm using SQL Server 2005.

Solution 1

You could try using the COLUMNPROPERTY() function.

DECLARE @value INT;
SELECT @value = COLUMNPROPERTY(OBJECT_ID('schema.table'), 'column_name', 'IsFulltextIndexed')

IF (@value = 1)
  PRINT 'Fulltext column'
ELSE
  PRINT 'No Fulltext column'

Solution 2

You can try something like this:

SELECT * 
FROM sys.columns c 
INNER JOIN sys.fulltext_index_columns fic ON c.object_id = fic.object_id 
                                          AND c.column_id = fic.column_id

If you need to limit it to a given table, use this:

SELECT * 
FROM sys.columns c 
INNER JOIN sys.fulltext_index_columns fic ON c.object_id = fic.object_id 
                                          AND c.column_id = fic.column_id
WHERE c.object_id = OBJECT_ID('YourTableNameHere')

Comments

[Kees C. Bakker]

Sweet, that works. But it seems Christian.K has a smaller answer.

[Kees C. Bakker]

That values are possible in this case? 0 and NULL?

[Christian.K]

Sorry, I edited the answer a couple of times, so I don't know what revision you're referring to. But according to MSDN: 0 - no fulltext indexing, 1 - fulltext indexing, NULL - error.

[webnoob]

I prefer this answer as it shows all the full-text indexed columns along with the column ID. This allows you to run something like SELECT display_term, column_id, document_count FROM sys.dm_fts_index_keywords (DB_ID('DB_NAME'), OBJECT_ID('TABLE_NAME')) order by document_count desc to show all the terms and where they are indexed.