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.
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.
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.
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.
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.
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'
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With