Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL: How to check if column is fulltext enabled?

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.

like image 478
Kees C. Bakker Avatar asked May 04 '12 09:05

Kees C. Bakker


People also ask

How do I know if full-text indexing is enabled?

A: You can determine if Full-Text Search is installed by querying the FULLTEXTSERVICEPROPERTY like you can see in the following query. If the query returns 1 then Full-Text Search is enabled.

How do I enable full text search in SQL?

SQL Server databases are full-text enabled by default. Before you can run full-text queries, however, you must create a full text catalog and create a full-text index on the tables or indexed views you want to search.

What is fulltext SQL?

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on the rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

How do I enable full-text index in SQL Server?

In Management Studio, in Object Explorer, expand the server. Expand Databases, and then expand the database that contains the full-text index. Expand Tables. Right-click the table on which the full-text index is defined, select Full-Text index, and on the Full-Text index context menu, click Properties.


2 Answers

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'
like image 199
Christian.K Avatar answered Oct 25 '22 09:10

Christian.K


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')
like image 26
marc_s Avatar answered Oct 25 '22 10:10

marc_s