16

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.

Kees C. Bakker
  • 32,294
  • 27
  • 115
  • 203
  • I think the last example from the answer to [this question](https://stackoverflow.com/questions/940386/sql-server-fti-how-to-check-table-status) might help. I haven't tested it, though. – Mihai Todor May 04 '12 at 09:19

2 Answers2

15

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'
Christian.K
  • 47,778
  • 10
  • 99
  • 143
  • That values are possible in this case? `0` and `NULL`? – Kees C. Bakker May 04 '12 at 09:43
  • 1
    Sorry, I edited the answer a couple of times, so I don't know what revision you're referring to. But according to [MSDN](http://msdn.microsoft.com/en-us/library/ms174968%28v=sql.90%29.aspx): 0 - no fulltext indexing, 1 - fulltext indexing, NULL - error. – Christian.K May 04 '12 at 09:44
14

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')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Sweet, that works. But it seems Christian.K has a smaller answer. – Kees C. Bakker May 04 '12 at 09:43
  • 2
    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. – webnoob Aug 03 '12 at 12:47