SQL Server - Script to update Full text Catalogue Index

Question

I have a full text search index in the development environment. It contains data of the table to be indexed and the coulmns selected for the full text indexing. I viewed it through Management Studio.

I need to update the selected columns of the index in the acceptance environment. Can I generate a script for this purpose.

I tried generating a script of the index by goining into "Storage" > Under "Full text Catalogs" > Right click on the Index > select "Script Catalog as" > select "Drop and Create to". But it gives a very basic script, The columns of the table which I need to associate in the index are not generated in the script. The script i get is as below:

DROP FULLTEXT CATALOG [MYTABLE]
GO

CREATE FULLTEXT CATALOG [MYTABLE]WITH ACCENT_SENSITIVITY = OFF

GO

But what I need is to change the selected columns of the table in the index.

Answer 1

You can generate the script for the full text index by scripting the table, but first you need to enable the Script full-text indexes option as follows:

1. In SQL Server Management Studio, click the Tools menu > Options
2. In the left pane select SQL Server Object Explorer > Scripting
3. In the right pane under Table and view options, set Script full-text indexes to True.

Once that option is set, right-click the table and select Script Table as > CREATE To (or DROP and CREATE To). The relevant part of the script looks something like this:

CREATE FULLTEXT INDEX ON [dbo].[Table1] (
   [Column1] LANGUAGE 'Neutral'
)
KEY INDEX [PK_Table1] ON ([MYTABLE], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

GO

(where MYTABLE is the name of the full text catalog, based on the original question)